Search code examples
sql-serverperformancet-sqlinsert-updatebulk

How to update and add new records at the same time

I have a table that contains over than a million records (products). Now, daily, I need to either update existing records, and/or add new ones.

Instead of doing it one-by-one (takes couple of hours), I managed to use SqlBulkCopy to work with bunch of records and managed to do my inserts in the matter of seconds, but it can handle only new inserts. So I am thinking about creating a new table that contains new records and old records; and then use that temporary table (on the SQL end) to update/add to the main table.

Any advice how can I perform that update?

Solution

  • One of the better ways to handle this is with the MERGE command in SQL. Mssqltips has a good tutorial on it, it can be a bit trickier to use than some of the other commands.

    Also, due to locking you may want to break this up into multiple smaller transactions, unless you know you can tolerate blocking during the update.