假设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
最好的写法是什么?
当前回答
请看我对之前一个非常相似的问题的详细回答
@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存在…其他的……至少要做两个请求(一个检查,一个采取行动)。下面的方法在记录存在时只需要一个,如果需要插入则需要两个:
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')
虽然现在评论这个有点晚了,但我想添加一个使用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之前检查潜在的约束违反 不同错误处理技术对性能的影响
Before everyone jumps to HOLDLOCK-s out of fear from these nafarious users running your sprocs directly :-) let me point out that you have to guarantee uniqueness of new PK-s by design (identity keys, sequence generators in Oracle, unique indexes for external ID-s, queries covered by indexes). That's the alpha and omega of the issue. If you don't have that, no HOLDLOCK-s of the universe are going to save you and if you do have that then you don't need anything beyond UPDLOCK on the first select (or to use update first).
scproc通常在非常受控的条件下运行,并假设有可信的调用者(中间层)。这意味着如果一个简单的upsert模式(更新+插入或合并)看到重复的PK,这意味着在中间层或表设计中存在错误,SQL将在这种情况下调用错误并拒绝记录。在这种情况下,放置HOLDLOCK除了降低性能外,还等于吃掉异常并接收潜在的错误数据。
话虽如此,使用MERGE或UPDATE然后INSERT在您的服务器上更容易,而且更容易出错,因为您不必记得在第一次选择时添加(UPDLOCK)。此外,如果您正在小批量地进行插入/更新,则需要了解数据,以便决定某个事务是否合适。如果它只是一个不相关记录的集合,那么额外的“包络”交易将是有害的。
在SQL Server 2008中,您可以使用MERGE语句