假设有一个表格结构如下: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提供的解决方案展示了一般性思路,但容易出错。
为避免死锁和主键冲突,可以使用类似于这样的方法:
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
看到我的类似之前问题的详细回答
@Beau Crawford在SQL 2005及以下版本中是一种不错的方法,但如果您要授予声望,则应该给予第一个在SO上发布的人。唯一的问题是对于插入仍然需要进行两个IO操作。
MS Sql2008从SQL:2003标准引入了merge
:
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操作,但代码很糟糕 :-(
upsert
。upsert
语法是一种更好的方式来完成这个操作,所以至少微软也应该支持它——毕竟在T-SQL中它并不是唯一的非标准关键词。 - KeithHOLDLOCK
。 - Keith如果存在记录则更新MyTable表中的FieldA字段,根据Key字段匹配。 否则, 如果没有匹配到任何记录,则插入一条新的记录到MyTable表中(FieldA)值为@FieldA。
很多人会建议你使用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;
很多人会建议这样做:
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捕获你本可以在第一时间预防的异常,这种情况代价更高,除非几乎每次插入操作都会失败。我在以下链接中已经证明:
IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)
编辑:
唉,尽管这样做的解决方案没有选择器似乎更好,因为它们可以在少一个步骤的情况下完成任务,但即使对我自己有害,我也必须承认这一点。
如果您想一次性插入多条记录,可以使用ANSI SQL:2003 DML语句MERGE。
MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
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;
/*
CREATE TABLE ApplicationsDesSocietes (
id INT IDENTITY(0,1) NOT NULL,
applicationId INT NOT NULL,
societeId INT NOT NULL,
suppression BIT NULL,
CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/
DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0
MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
AS source (applicationId, societeId, suppression)
--here goes the ON join condition
ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
UPDATE
--place your list of SET here
SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
--insert a new line with the SOURCE table one row
INSERT (applicationId, societeId, suppression)
VALUES (source.applicationId, source.societeId, source.suppression);
GO
将表名和字段名更改为所需的内容。 注意 使用 ON 条件。 然后在 DECLARE 行中设置变量的适当值(和类型)。
干杯。
这取决于使用模式。我们必须从大局出发看待使用情况,而不被细节所迷惑。例如,如果使用模式是99%的记录创建后更新,则“UPSERT”是最佳解决方案。
在第一次插入(命中)之后,所有语句都将进行单个更新,没有任何条件限制。插入时的“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
MERGE
语句,该语句用于在数据不存在时插入数据或者在数据已存在时更新数据。MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`