I have a datatable comimg from code that contain set of columns (ID- primary key, and name varchar). I have my sql table that also contain same (ID- primary key, and name varchar) type of data. table has 200000+ rows. I want to compare that if sqltable id column contain same id in datatable means (datatable id column value matches with sqltable id field). If the both id is same thn it will overwrite recoed in sqltable.
Vivek Jagga Chandigarh
My assumption is that the datatable rows all have a state of "Added", so will result in the InsertCommand being executed.
Option 1 - create a stored proc that checks whether a record with the ID already exists first. If it exists, do an UPDATE, else do an INSERT. Assign this sproc as the InsertCommand
Option 2 (assumes SQL 2005+) - create a stored proc that tries an INSERT within a TRY block. In the CATCH block, if the error is a PK constraint error (ERROR_NUMBER() = 2627, then it means the record with that ID already exists so do an UPDATE on it. Assign this sproc as the InsertCommand
Option 3 - load all the data in the datatable in to a new table (use the SqlBulkCopy class for this). Then UPDATE records in the real table from this table where the ID already exists. Then INSERT records into the real table where they don't already exist.
Option 1 has the overhead of checking each time whether a record exists before doing anything and this could be relatively expensive over the whole activity.
Option 2 is more optimal if the majority of the time, you know that most of the records are NEW (i.e. if there's quite a few updates, then you'll have the hit of the PK errors ).
Option 3 can work and perform really well. The SqlBulkCopy class is a fast way to bulk load data into the database.