在 SQL Server 上插入更新存储过程 [英] Insert Update stored proc on SQL Server

问题描述

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

I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this:

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

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

My logic behind writing it in this way is that the update will perform an implicit select using the where clause and if that returns 0 then the insert will take place.

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

The alternative to doing it this way would be to do a select and then based on the number of rows returned either do an update or insert. This I considered inefficient because if you are to do an update it will cause 2 selects (the first explicit select call and the second implicit in the where of the update). If the proc were to do an insert then there'd be no difference in efficiency.

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

Is my logic sound here? Is this how you would combine an insert and update into a stored proc?

推荐答案

你的假设是正确的，这是实现它的最佳方式，它被称为 更新插入/合并.

Your assumption is right, this is the optimal way to do it and it's called upsert/merge.

UPSERT 的重要性 - 来自 sqlservercentral.com:

对于上述案例中的每个更新，我们都会删除一个如果我们从表中额外读取使用 UPSERT 而不是 EXISTS.不幸的是，对于插入，UPSERT 和 IF EXISTS 方法使用表上的读取次数相同.因此检查是否存在应该只在有非常正当的理由来证明额外的 I/O.优化的方式做事是为了确保你尽可能少阅读数据库.

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.

最好的策略是尝试更新.如果没有行受更新然后插入.多数情况在这种情况下，该行将已经存在并且只有一个 I/O需要.

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.

编辑:请查看这个答案和链接的博客文章了解此模式的问题以及如何使其安全运行.

Edit: Please check out this answer and the linked blog post to learn about the problems with this pattern and how to make it work safe.

这篇关于在 SQL Server 上插入更新存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！