Sql server bulk insert/update vs MERGE in insert or update scenario

Question

I need to find the best way to insert or update data in database using sql server and asp.net. It is a standard scenario if data exist it is updated if not it is inserted. I know that there are many topic here about that but no one has answered what i need to know.

So my problem is that there is really no problem when you update/insert 5k - 10k rows but what with 50k and more.

My first idea was to use sql server 2008 MERGE command, but i have some performance consideration if it will be 50k+ rows. Also i don't know if i can marge data this way based not on primary id key (int) but on other unique key in the table. (to be precise an product serial number that will not change in time).

My second idea was to first get all product serials, then compare the new data serials with that and divide it into data to insert and data to update, then just make one bulk insert and one bulk update.

I just don't know which will be better, with MERGE i don't know what the performance will be and it is supported only by sql server 2008, but it looks quite simple, the second option doesn't need sql 2008, the batches should be fast but selecting first all serials and dividing based on them could have some performance penalties.

What is you opinion, what to choose ?

Solution 1

Merge performace way better because "One of the most important advantage of MERGE statement is all the data is read and processed only once"

You dont need a primary key, you can join on one or more fields what makes your records unique

Solution 2

There should be no problem performing the merge on the serial number as you've described it. You may want to read Optimizing MERGE Statement Performance for Microsoft's recommended best practices when using MERGE.

Comments

[Denis Valeev]

There's no bulk merge. How can it be way better in the bulk insert and update context?

[Ivo]

He is inserting/updating 50k records, thats bulk imo , put these in a temp table and merge that on with the original

[Programista]

Join for unique record will not be good in this scenario, it must be by this unique serial. The data will come from other companies in a file/text based format that i will need first to precess and then make marge or my second option. So the product serial is that one that will guarantee no problems with data synchronization between my database and their data and i even do not need to know or adjust my database schema.