How to Compare every record in Excel file and save to Database Table.

[Ehtesham Siddiq]

Hi,

I have my application in VS2008 and coded Vb.net.

My query is i want to save record from an excel sheet to my Database Table.

But i dont want to insert the Excel sheet as a whole rather i want to insert the record in my database table Row wise.Inserting the records is not the only thing but my major issue is i want to check and compare the Primary Key column of my Table with the Column in my Excel.

Suppose Table1 is my Sql Database Table,and RegistrationNo is the Primary Key Column.Similarly there is also a column of Registration Number in my Excel Sheet.

Now before inserting each record the RegistrationNo column should be compared.If the a registrationNo in the excel sheet say RegistrationNo 112 is already present in the table then it should not Insert the record and if its not present then it should insert.

Also the other issue is the number of columns in my Database table are more than the number of columns in my excel Sheet.So the columns that are not present in the excel sheet should be set to NULL while inserting the record in the Table.

Please help me with a link of similar example or any helpful code to do this.

[Phasma]

What version of EXCEL are you using and what is your database?

That said you could dump the excel sheet and Database Table into a dataset and do the compare from there. That will require fewer trips to the DB and EXCEL. Then only add the ones that are unique to the Database Table.

[adam_k]

I'd insert the sheet into a temp table (or a var table for speed) and have SQL pick what it needs with a not in where clause or an outer join with null values for the table side key columns.
This will give you the possibility to verify multiple keys simultaneously with one go, without transfering the data from the sql server.