SQL Bulk Data Insertion OR Updation

Question

Ok so here it goes. The requirement I am facing is that I have a table in the database say "MyDbTable" which has a lot of data in it.

Lets say it has three columns like:

ColA    ColB    ColC

1           a        ab
2           b        bc
3           c        cd

ColA being the identity

Now I call a webservice and expected result consists of around 1500 rows.

the schema is same of the returned result say:

ColA    ColB    ColC

1           a        xy
3           c        yz
4           c        yz

Now what I really want to do is check for the existing records I have in the service results and update them in MyDbTable, in this case, its gonna be the records with 1 and 3 in ColA. I will have to update them. For the record with ColA value 4 in the webservice result is new so I would have to insert it.

Now the problem is that the MyDbTable have thousands of rows and the service also returns a number of rows.

I know the simplest way to do it is the brute force where we iterate over each record, check it and then handle it, either through application or a stored procedure.

What I really wanna know is how to do this in the most optimized way.

Answer 1

This is what I'd assume to be the most efficient way (haven't measured it, though):

1. Copy the whole table returned from the web service into a temporary table of your database, using a bulk copy operation such as offered by the SqlBulkCopy Class.

2. Use a single SQL Server MERGE statement to make an "update-or-insert" operation from the temporary table into your real table.

If you are using SQL Server version older than 2008, you can use an UPDATE statement with an OUTPUT clause instead of a MERGE.

Answer 2

Well you can do one thing...

take first row from web service and take it's ID Say "1" And pass it to GetRecordFromDB method...

Now if that method return some object then set it's values form those values that you found from Webservice.. and then call a method to update records in database.

So code logic would be

object updateorinsert = library.getobjectbyid(IDFROMFirstRecordOfWebservice)
if (updateorinsert  != null)
   {
           updateorinsert.ColAValue = WebserviceData.ColAValue
   }

  updateorinsert.ColBValue = WebserviceData.ColBValue
  updateorinsert.ColCValue = WebserviceData.ColCValue

library.UpdateRecordOrInsertInDB(updateorinsert);

You can use the SqlBulkCopy as well and using the staging table you can perform Inser / Update that suits all your need.

You can do that all those ID'S that are there in staging table and in actual table should be removed after BulkInsert and then copy all records from staging to actual.

Using SQLBulkCopy to Insert/Update database

I hope the above link helps.