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
相关文章