Sql Server 批量插入/更新与插入或更新场景中的 MERGE
我需要找到使用 sql server 和 asp.net 在数据库中插入或更新数据的最佳方法。这是一个标准场景，如果数据存在，则更新，如果不存在，则插入。我知道这里有很多关于此的主题，但没有人回答我需要知道的内容。
所以我的问题是，当您更新/插入 5k - 10k 行时确实没有问题，但更新/插入 50k 及更多行时会出现什么问题。
我的第一个想法是使用 sql server 2008 MERGE 命令，但如果它是 50k+ 行，我会考虑一些性能。另外，我不知道是否可以根据表中的其他唯一键而不是主 id 键（int）来以这种方式标记数据。 （准确地说是不会随时间变化的产品序列号）。
我的第二个想法是，首先获取所有产品序列号，然后将新的数据序列号与该序列号进行比较，并将其分为要插入的数据和要更新的数据，然后只进行一次批量插入和一次批量更新。
我只是不知道哪个会更好，使用MERGE我不知道性能会怎样，而且它仅受sql server 2008支持，但它看起来很简单，第二个选项不需要sql 2008，批次应该很快，但首先选择所有序列并根据它们进行划分可能会产生一些性能损失。
您有何看法，该选择什么？
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 ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
合并性能更好，因为“MERGE 语句最重要的优点之一是所有数据仅读取和处理一次”
您不需要主键，您可以加入一个或多个字段，这使您的记录独一无二
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
正如您所描述的那样，对序列号执行合并应该没有问题。您可能需要阅读优化 MERGE 语句性能，了解 Microsoft 推荐的最佳实践：使用合并。
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.