sql - 在 SQL Server 上插入更新存储过程

Question

我编写了一个存储过程，如果记录存在，它将执行更新，否则它将执行插入。它看起来像这样:

update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)

我以这种方式编写的逻辑是，更新将使用 where 子句执行隐式选择，如果返回 0，则将进行插入。

这种方式的替代方法是进行选择，然后根据返回的行数进行更新或插入。我认为这是低效的，因为如果你要进行更新，它将导致 2 个选择(第一个显式选择调用，第二个隐式在更新位置)。如果进程要执行插入操作，那么效率上不会有任何差异。

我的逻辑合理吗？这是您将插入和更新组合到存储过程中的方式吗？

Answer 1

你的假设是正确的，这是最好的方法，它被称为 upsert/merge .

Importance of UPSERT - from sqlservercentral.com :

For every update in the case mentioned above we are removing one additional read from the table if we use the UPSERT instead of EXISTS. Unfortunately for an Insert, both the UPSERT and IF EXISTS methods use the same number of reads on the table. Therefore the check for existence should only be done when there is a very valid reason to justify the additional I/O. The optimized way to do things is to make sure that you have little reads as possible on the DB.

The best strategy is to attempt the update. If no rows are affected by the update then insert. In most circumstances, the row will already exist and only one I/O will be required.

编辑:请查看this answer以及链接的博客文章，了解此模式的问题以及如何使其安全工作。