在SQL服务器上插入更新存储的proc

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:

我已经编写了一个存储的proc，如果存在记录，它将进行更新，否则将进行插入。它看起来是这样的:

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.

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

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.

另一种方法是进行选择，然后根据返回的行数进行更新或插入。我认为这是低效的，因为如果要进行更新，会导致2个选择(第一个显式的选择调用和第二个隐式的更新)。如果proc进行插入操作，那么效率就没有差别。

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

我的逻辑是这样的吗?这就是将插入和更新合并到存储proc中的方法吗?

9 个解决方案

#1

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

你的假设是对的，这是最优的方法，叫做upsert/merge。

Importance of UPSERT - from sqlservercentral.com:

UPSERT的重要性——来自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.

对于上面提到的每个更新，如果我们使用UPSERT而不存在，我们将从表中删除一个额外的读取。不幸的是，对于插入，UPSERT和IF都使用相同数量的表读取。因此，仅当存在一个非常有效的理由来证明附加的I/O时，才应该检查是否存在。优化的方法是确保在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.

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

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.

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

#2

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.

要快速回答，请尝试以下模式。它将在SQL 2000和以上版本中正常工作。SQL 2005提供了错误处理，它打开了其他选项，SQL 2008给了您一个合并命令。

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

#3

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.

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

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

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

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

如前所述，应该在可用的地方使用MERGE。

#4

MERGE is one of the new features in SQL Server 2008, by the way.

顺便说一下，MERGE是SQL Server 2008中的一个新特性。

#5

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.

也许添加@@error检查和回滚也是个好主意。

#6

If you are not doing a merge in SQL 2008 you must change it to:

如果您在SQL 2008中没有合并，您必须将其更改为:

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

如果@@rowcount =0， @@error=0。

otherwise if the update fails for some reason then it will try and to an insert afterwards because the rowcount on a failed statement is 0

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

#7

Big fan of the UPSERT, really cuts down on the code to manage. Here is another way I do it: One of the input parameters is ID, if the ID is NULL or 0, you know it's an INSERT, otherwise it's an update. Assumes the application knows if there is an ID, so wont work in all situations, but will cut the executes in half if you do.

大粉丝的维护，真的削减了代码管理。另一种方法是:输入参数之一是ID，如果ID为NULL或0，你知道它是插入，否则就是更新。假设应用程序知道是否有一个ID，所以在所有情况下都不会工作，但是如果您这样做，将会将执行减少一半。

#8

Your logic seems sound, but you might want to consider adding some code to prevent the insert if you had passed in a specific primary key.

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

Otherwise, if you're always doing an insert if the update didn't affect any records, what happens when someone deletes the record before you "UPSERT" runs? Now the record you were trying to update doesn't exist, so it'll create a record instead. That probably isn't the behavior you were looking for.

否则，如果更新没有影响任何记录，如果您总是执行插入操作，那么当某人在“UPSERT”运行之前删除记录时，会发生什么情况?现在您试图更新的记录不存在，因此它将创建一个记录。这可能不是你想要的行为。

#9

Modified Dima Malenko post:

修改迪玛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

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.

您可以捕获错误并将记录发送到失败的插入表。我需要这样做，因为我们正在使用通过WSDL发送的任何数据，如果可能的话，在内部修复它。

#1