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

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

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

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

这样做的替代方法是做一个select,然后根据返回的行数进行更新或插入。 这是我认为效率低下的原因,因为如果您要进行更新,它将导致2个选择(第一个显式选择调用,第二个隐含在更新的位置)。 如果proc做插入,那么效率就没有区别了。

我的逻辑在这里发声吗? 这是如何将插入和更新结合到存储过程中的?


你的假设是正确的,这是实现它的最佳方式,它被称为upsert / merge。

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

对于上述情况中的每个更新,如果我们使用UPSERT而不是EXISTS,我们将从表中删除一个附加读取。 不幸的是,对于Insert,UPSERT和IF EXISTS方法在表上使用相同数量的读取。 因此,只有在有非常有效的理由证明额外的I / O时才应该检查存在性。 优化的做事方式是确保在数据库上尽可能少地读取数据。

最好的策略是尝试更新。 如果没有行受更新影响,则插入。 在大多数情况下,该行已经存在,只需要一个I / O。

编辑 :请查看这个答案和链接的博客文章,了解这种模式的问题以及如何使其安全工作。


请阅读我的博客上的帖子,以获取可以使用的良好,安全的模式。 有很多考虑因素,关于这个问题的接受答案远不是安全的。

为了快速回答,请尝试以下模式。 它将在SQL 2000及更高版本上正常工作。 SQL 2005为您提供了错误处理,从而打开了其他选项,SQL 2008为您提供了MERGE命令。

begin tran
   update t with (serializable)
   set hitCount = hitCount + 1
   where pk = @id
   if @@rowcount = 0
   begin
      insert t (pk, hitCount)
      values (@id,1)
   end
commit tran

如果要与SQL Server 2000/2005一起使用,需要将原始代码包含在事务中以确保数据在并发场景中保持一致。

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

这将导致额外的性能成本,但会确保数据的完整性。

如已经建议的那样,应该在可用的地方使用MERGE。

链接地址: http://www.djcxy.com/p/19785.html

上一篇: Insert Update stored proc on SQL Server

下一篇: Why is “while ( !feof (file) )” always wrong?