Geek Answers Handbook

Sql bulk insert / upgrade versus MERGE in insert or upgrade script

I need to find the best way to insert or update data in a database using SQL Server and asp.net. This is a standard scenario, if data exists, it is updated if it is not inserted. I know there are a lot of topics, but no one answered what I need to know.

So, my problem is that when updating / inserting rows 5k - 10k, but with 50 thousand or more, there are no problems.

My first idea was to use the SQL Server SQL Server command, but I have a performance score if it is 50k + rows. In addition, I do not know if I can transfer data in this way, based not on the main key ID (int), but on another unique key in the table. (more precisely, the serial number of the product, which will not change in time).

My second idea was to first get all the serial products, then compare the new serial data with them and split them into insert data and update data, and then just do one bulk insert and one bulk update.

I just don’t know what will be better, with MERGE I don’t know what the performance will be, and only SQL Server 2008 is supported, but it looks pretty simple, the second option is not needed. sql 2008, parties should be fast, but first selecting all series and dividing them may have some performance penalties.

What do you think, what to choose?

+6
c # merge sql-server bulkinsert
source share
2 answers

Merging is better because "One of the most important advantages of the MERGE operator is that all data is read and processed only once."

You do not need a primary key, you can join one or more fields, which makes your records unique

+4
source share

There should be no problem with merging the serial number as you described it. You might want to read the “MERGE Report Performance Optimization" recommended by Microsoft when using MERGE .

+2
source share

More articles:


All Articles