Overwrite values in database by a unique column

I have a datatable comimg from code that contain set of columns (ID- primary key, and name varchar). I have my sql table that als开发者_C百科o 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.