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

发布于 2024-07-04 06:03:34 字数 439 浏览 10 评论 0原文

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

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

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

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

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

原文

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)

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.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

美羊羊 2024-07-11 06:03:34

您的逻辑似乎是合理的，但如果您传入了特定的主键，您可能需要考虑添加一些代码以防止插入。

否则，如果您总是在更新不影响任何记录的情况下进行插入，那么当有人在您运行“UPSERT”之前删除该记录时会发生什么？现在您尝试更新的记录不存在，因此它将创建一条记录。这可能不是您正在寻找的行为。

回复收藏 0 原文

恏ㄋ傷疤忘ㄋ疼 2024-07-11 06:03:34

修改了 Dima Malenko 帖子：

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 

IF @@Error > 0 
  BEGIN 
      INSERT INTO MYERRORTABLE 
                  (ID, 
                   COL1, 
                   COL2) 
      VALUES      (@ID, 
                   @col1, 
                   @col2) 
  END 

COMMIT TRANSACTION UPSERT

您可以捕获错误并将记录发送到失败的插入表。
我需要这样做，因为我们正在获取通过 WSDL 发送的所有数据，并在可能的情况下在内部修复它。

Modified Dima Malenko post:

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 

IF @@Error > 0 
  BEGIN 
      INSERT INTO MYERRORTABLE 
                  (ID, 
                   COL1, 
                   COL2) 
      VALUES      (@ID, 
                   @col1, 
                   @col2) 
  END 

COMMIT TRANSACTION UPSERT

You can trap the error and send the record to a failed insert table.
I needed to do this because we are taking whatever data is send via WSDL and if possible fixing it internally.

回复收藏 0 原文

两个我 2024-07-11 06:03:34

UPSERT 的忠实粉丝，确实减少了需要管理的代码。这是我的另一种方法：输入参数之一是 ID，如果 ID 为 NULL 或 0，则说明这是一个 INSERT，否则是一个更新。假设应用程序知道是否有 ID，因此不会在所有情况下都工作，但如果知道的话会将执行次数减少一半。

回复收藏 0 原文

中二柚 2024-07-11 06:03:34

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

if @@rowcount = 0 and @@error=0

否则，如果更新由于某种原因失败，那么它将尝试随后插入，因为失败语句为 0

回复收藏 0 原文

甚是思念 2024-07-11 06:03:34

请阅读我的博客上的帖子以获得良好、安全的模式您可以使用。有很多考虑因素，并且关于这个问题的公认答案远非安全。

要快速获得答案，请尝试以下模式。它在 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

Please read the post on my blog for a good, safe pattern you can use. There are a lot of considerations, and the accepted answer on this question is far from safe.

For a quick answer try the following pattern. It will work fine on SQL 2000 and above. SQL 2005 gives you error handling which opens up other options and SQL 2008 gives you a MERGE command.

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

回复收藏 0 原文

半暖夏伤 2024-07-11 06:03:34

如果要与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。

If to be used with SQL Server 2000/2005 the original code needs to be enclosed in transaction to make sure that data remain consistent in concurrent scenario.

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

This will incur additional performance cost, but will ensure data integrity.

Add, as already suggested, MERGE should be used where available.

回复收藏 0 原文

叫嚣ゝ 2024-07-11 06:03:34

您的假设是正确的，这是执行此操作的最佳方法，称为 upsert/merge。

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

对于上述案例中的每一项更新，我们都会删除一项
如果我们从表中额外读取
使用 UPSERT 而不是 EXISTS。
不幸的是，对于插入物来说，两者
UPSERT 和 IF EXISTS 方法使用
表上的读取次数相同。
因此检查存在性
仅当存在以下情况时才应进行
非常充分的理由来证明
额外的 I/O。优化的方式
做事是为了确保你
尽可能少读
数据库。
最好的策略是尝试
更新。如果没有行受到影响
更新然后插入。多数情况
在这种情况下，该行已经
存在且只有一个 I/O
必填。

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

回复收藏 0 原文

一身仙ぐ女味 2024-07-11 06:03:34

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

回复收藏 0 原文

故事未完 2024-07-11 06:03:34

你不仅需要在事务中运行它，还需要高隔离级别。事实上，默认隔离级别是已提交读，并且此代码需要可序列化。

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

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

You not only need to run it in transaction, it also needs high isolation level. I fact default isolation level is Read Commited and this code need 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

Maybe adding also the @@error check and rollback could be good idea.

回复收藏 0 原文

~没有更多了~