在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,则插入将发生.

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

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

你的假设是正确的,这是做到这一点的最佳方式,它被称为upsert/merge.

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

对于上述情况中的每次更新,如果我们使用UPSERT而不是EXISTS,我们将从表中删除一个额外的读数.不幸的是,对于插入,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应该在可用的地方使用.

顺便说一句,MERGE是SQL Server 2008中的新功能之一.

您不仅需要在事务中运行它,还需要高隔离级别.事实上,默认隔离级别是Read Commited,此代码需要Serializable.

SET transaction isolation level SERIALIZABLE
BEGIN TRANSACTION Upsert
UPDATE myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
  begin
    INSERT into myTable (ID, Col1, Col2) values (@ID @col1, @col2)
  end
COMMIT TRANSACTION Upsert

也许添加@@错误检查和回滚可能是个好主意.

如果您没有在SQL 2008中进行合并,则必须将其更改为:

if @@ rowcount = 0和@@ error = 0

否则,如果更新由于某种原因失败,那么它将尝试之后插入,因为失败语句上的rowcount为0