假设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
最好的写法是什么?
当前回答
不要忘记事务。性能很好,但是简单的方法(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
其他回答
做一个UPSERT:
UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key IF @@ROWCOUNT = 0 INSERT INTO MyTable (FieldA) VALUES (@FieldA)
http://en.wikipedia.org/wiki/Upsert
我通常会像其他几个帖子说的那样,先检查它是否存在,然后再做任何正确的路径。在这样做时,您应该记住的一件事是,sql缓存的执行计划对于一个路径或另一个路径可能不是最优的。我认为最好的方法是调用两个不同的存储过程。
FirstSP: If Exists Call SecondSP (UpdateProc) Else Call ThirdSP (InsertProc)
现在,我不经常听从自己的建议,所以对我的建议半信半疑。
虽然现在评论这个有点晚了,但我想添加一个使用MERGE的更完整的示例。
这样的Insert+Update语句通常被称为“Upsert”语句,可以在SQL Server中使用MERGE实现。
这里举了一个很好的例子: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
上面还解释了锁定和并发场景。
我将引用同样的内容作为参考:
ALTER PROCEDURE dbo.Merge_Foo2
@ID int
AS
SET NOCOUNT, XACT_ABORT ON;
MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
ON f.ID = new_foo.ID
WHEN MATCHED THEN
UPDATE
SET f.UpdateSpid = @@SPID,
UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
INSERT
(
ID,
InsertSpid,
InsertTime
)
VALUES
(
new_foo.ID,
@@SPID,
SYSDATETIME()
);
RETURN @@ERROR;
许多人会建议您使用MERGE,但我提醒您不要使用它。默认情况下,它不会像多条语句那样保护你不受并发性和竞态条件的影响,它还会带来其他危险:
注意SQL Server的MERGE语句 那么,你想使用MERGE,嗯?
即使使用这种“更简单”的语法,我仍然更喜欢这种方法(为简洁起见,省略了错误处理):
BEGIN TRANSACTION;
UPDATE dbo.table WITH (UPDLOCK, SERIALIZABLE)
SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;
请停止使用此UPSERT反模式
很多人会这样建议:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
UPDATE ...
END
ELSE
BEGIN
INSERT ...
END
COMMIT TRANSACTION;
但是这样做的目的是确保您可能需要读取表两次来定位要更新的行。在第一个示例中,您只需要定位一次行。(在这两种情况下,如果从初始读取中没有找到行,则会发生插入。)
其他人会这样建议:
BEGIN TRY
INSERT ...
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
UPDATE ...
END CATCH
但是,如果不考虑其他原因,让SQL Server捕获您本来可以在第一个位置防止的异常代价要高得多,那么这就有问题了,除非在几乎所有插入都失败的罕见情况下。我在这里证明:
在进入TRY/CATCH之前检查潜在的约束违反 不同错误处理技术对性能的影响
您可以使用这个查询。适用于所有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
注意:请解释否定答案