I'm importing 3 thousands rows from Excel Sheet using ExcelDataReader to Datatable.
How can i validate these rows before inserting them into database using SqlBulkCopy.Writetoserver().
1. How to compare my datatable to the TableScheme of one of the SQL Server Tables.
2. Can i Validate FK Constraints and PK Duplications before insertion
3. if i've some table like this with SponsorID Column with FK to the Same Table
ID - Name - Age - Relation - Column4 - SponsorID
100 - Adam Michale - 32 - himself - 1/1/2015 - 100
101 - Anna Mike - 29 - wife - 1/1/2015 - 100
102 - Sarah Adam - 6 - Daughter - 1/1/2015 - 100
is there anyway to do such insertion using SqlBulkCopy.
Thanks in advance.
And I'll be more than happy to hear about any other approaches for insertions like this if there are any
You can query your excel file and compare that using SQL language. See example below:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
Find more options about Distributed queries on this link.
You can query the data that you already have on the sql data and compare it with excel in order to verify PK/entry duplications.
You can create a column OldID, with the IDs present on the excel. Then update the Sponsor ID with the new IDs using the OldID reference.