如何同时更新和添加新记录 [英] 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.

我不是一个接一个地做(要花几个小时)，而是设法使用 SqlBulkCopy 处理一堆记录，并在几秒钟内完成了插入操作，但是它只能处理新的插入.因此，我正在考虑创建一个包含新记录和旧记录的新表；然后使用该临时表(在SQL端)来更新/添加到主表中.

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?

推荐答案

更好的方法之一是使用

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.

这篇关于如何同时更新和添加新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！