SQL Server 上的 INSERT OR UPDATE 解决方案 [英] Solutions for INSERT OR UPDATE on SQL Server

问题描述

假设表结构为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?

推荐答案

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

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 解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！