SQL Server上INSERT OR UPDATE的解决方案 [英] Solutions for INSERT OR UPDATE on SQL Server
问题描述
假设 MyTable(KEY,datafield1,datafield2 ...)
的表结构。
基本上:
pre>
IF(key exists)
运行更新命令
ELSE
运行insert命令
忘记交易。性能是好的,但简单(如果EXISTS ..)方法是非常危险的。
当多个线程将尝试执行插入或更新时,您可以轻松地
获得主键冲突。 p>
由@Beau Crawford& @Esteban显示一般想法,但容易出错。
为了避免死锁和PK违规,您可以使用以下方法:
begin tran
如果存在(select * from table with(updlock,serializable)where key = @key)
begin
更新表集。 ..
其中key = @key
end
else
begin
insert into table(key,...)
values(@key,。 ..)
end
commit tran
或
$ b
begin tran
更新表(可序列化)设置...
其中key = @key
$ b b如果@@ rowcount = 0
begin
insert into table(key,...)values(@key,..)
end
commit tran
Assume a table structure of MyTable(KEY, datafield1, datafield2...)
.
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
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.
Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.
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
or
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屋!