SQL Server 上 INSERT OR UPDATE 的解决方案

问题描述

假设表结构为MyTable(KEY, datafield1, datafield2...).

我经常想更新现有记录，或者在不存在的情况下插入新记录.

Often I want to either update an existing record, or insert a new record if it doesn't exist.

本质上:

IF (key exists)
  run update command
ELSE
  run insert command

最好的编写方式是什么?

What's the best performing way to write this?

推荐答案

不要忘记事务.性能不错，但简单(IF EXISTS..)方法非常危险.
当多个线程将尝试执行插入或更新时，您可以轻松地获取主键违规.

don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.
When multiple threads will try to perform Insert-or-update you can easily get primary key violation.

@Beau Crawford & 提供的解决方案@Esteban 展示了总体思路，但容易出错.

Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

为了避免死锁和PK违规，您可以使用以下内容:

To avoid deadlocks and PK violations you can use something like this:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

或

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

这篇关于SQL Server 上 INSERT OR UPDATE 的解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！