假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
当前回答
您可以使用这个查询。适用于所有SQL Server版本。它简单明了。但是您需要使用两个查询。如果不能使用MERGE,可以使用
BEGIN TRAN
UPDATE table
SET Id = @ID, Description = @Description
WHERE Id = @Id
INSERT INTO table(Id, Description)
SELECT @Id, @Description
WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)
COMMIT TRAN
注意:请解释否定答案
其他回答
做一个if存在…其他的……至少要做两个请求(一个检查,一个采取行动)。下面的方法在记录存在时只需要一个,如果需要插入则需要两个:
DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')
请看我对之前一个非常相似的问题的详细回答
@Beau Crawford's在SQL 2005及以下是一个很好的方法,尽管如果你授予rep它应该去第一个人SO它。唯一的问题是对于插入,它仍然是两个IO操作。
MS Sql2008引入了SQL:2003标准的合并:
merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
as source (field1, field2)
on target.idfield = 7
when matched then
update
set field1 = source.field1,
field2 = source.field2,
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, source.field1, source.field2, ... )
现在它真的只是一个IO操作,但糟糕的代码:-(
不要忘记事务。性能很好,但是简单的方法(IF EXISTS..)非常危险。 当多个线程将尝试执行插入或更新时,您可以轻松地 获取主键冲突。
由@Beau Crawford和@Esteban提供的解决方案显示了大致的想法,但容易出错。
为了避免死锁和PK违规,你可以使用如下方法:
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的REPEATABLE READ事务隔离级别:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION
IF (EXISTS (SELECT * FROM myTable WHERE key=@key)
UPDATE myTable SET ...
WHERE key=@key
ELSE
INSERT INTO myTable (key, ...)
VALUES (@key, ...)
COMMIT TRANSACTION
当当前运行的事务处于打开状态时,此隔离级别将阻止/阻止后续可重复读事务访问同一行(WHERE key=@key)。 另一方面,对另一行的操作不会被阻塞(WHERE key=@key2)。
这取决于使用模式。一个人必须在不迷失在细节的情况下看到使用的大局观。例如,如果使用模式是在创建记录后99%更新,那么'UPSERT'是最佳解决方案。
在第一次插入(hit)之后,它将全部是单个语句更新,没有if或but。插入的'where'条件是必要的,否则它将插入重复的数据,并且您不希望处理锁定。
UPDATE <tableName> SET <field>=@field WHERE key=@key;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO <tableName> (field)
SELECT @field
WHERE NOT EXISTS (select * from tableName where key = @key);
END