SQL服务器上插入或更新的解决方案

假设表结构为MyTable(KEY, datafield1, datafield2...)。

通常我想要更新现有记录，或者如果不存在则插入新记录。

基本上：

IF (key exists)
run update command
ELSE
run insert command

写这篇文章的最佳方式是什么？

855915 次浏览
小开

做一个选择，如果你得到一个结果，更新它，如果没有，创建它。

小开 
IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)

编辑：

唉，即使对我自己不利，我必须承认，没有选择的解决方案似乎更好，因为它们用更少的步骤完成任务。

小开

做一个UPSERT：

UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key


IF @@ROWCOUNT = 0
INSERT INTO MyTable (FieldA) VALUES (@FieldA)

http://en.wikipedia.org/wiki/Upsert

小开
最佳答案

不要忘记事务。性能很好，但简单的（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

或

begin tran
update table with (serializable) set ...
where key = @key


if @@rowcount = 0
begin
insert into table (key, ...) values (@key,..)
end
commit tran
小开

做一个如果存在……否则……涉及至少做两个请求（一个用于检查，一个用于采取行动）。以下方法在记录存在的情况下只需要一个，如果需要插入，则需要两个：

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')
小开

我通常会像其他一些海报所说的那样，首先检查它是否存在，然后做任何正确的路径。这样做时你应该记住的一件事是，sql缓存的执行计划对于一条路径或另一条路径可能不是最佳的。我相信最好的方法是调用两个不同的存储过程。

FirstSP:
If Exists
Call SecondSP (UpdateProc)
Else
Call ThirdSP (InsertProc)

现在，我不经常听从自己的建议，所以带着一粒盐。

小开

如果您使用ADO.NET，DataAdapter会处理此问题。

如果你想自己处理，是这样的：

确保键列上有主键约束。

然后你：

  1. 进行更新
  2. 如果更新失败是因为具有键的记录已经存在，请执行插入。如果更新没有失败，您就完成了。

您也可以反过来做，即先插入，如果插入失败则进行更新。通常第一种方式更好，因为更新比插入更频繁。

小开

在SQLServer 2008中，您可以使用MERGE语句

小开

如果您想一次UPSERT多个记录，您可以使用ANSISQL： 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 ...])

查看在SQLServer 2005中模拟MERGE语句。

小开

MSSQLServer 2008引入了MERGE语句，我相信它是SQL： 2003标准的一部分。正如许多人所表明的那样，处理一行情况并不是什么大问题，但是在处理大型数据集时，需要一个光标，随之而来的所有性能问题。在处理大型数据集时，MERGE语句将非常受欢迎。

小开

看到我的一个非常相似的问题的详细答案

@博克劳福德在SQL2005及以下版本中是一个很好的方法，但如果您授予rep，它应该转到第一个这么做的人。唯一的问题是，对于插入，它仍然是两个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操作，但可怕的代码：-（

小开

虽然现在评论这个已经很晚了，但我想添加一个使用MERGE的更完整的示例。

这种插入+更新语句通常称为“更新”语句，可以在SQLServer中使用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;
小开

在每个人都跳到HOLDLOCK-s之前，因为害怕这些直接运行你的sproc的疯狂用户：-）让我指出你必须通过设计来保证新PK-s的独特性（身份密钥、Oracle中的序列生成器、外部ID的唯一索引、索引覆盖的查询）。这是问题的重点。如果你没有，宇宙中没有HOLDLOCK-s能拯救你，如果你有，那么你在第一次选择时（或首先使用更新）不需要UPDLOCK之外的任何东西。

Sproc通常在非常受控的条件下运行，并假设有一个受信任的调用者（中间层）。这意味着如果一个简单的向上插入模式（更新+插入或合并）看到重复的PK，这意味着你的中间层或表设计中bug，SQL在这种情况下大喊错误并拒绝记录是很好的。在这种情况下放置HOLDLOCK等于吃异常和接收潜在的错误数据，除了降低你的性能之外。

话虽如此，使用MERGE或UPDATE然后INSERT在您的服务器上更容易，并且不容易出错，因为您不必记住在第一次选择时添加（UPDLOCK）。此外，如果您是在小批量进行插入/更新，您需要了解您的数据以确定事务是否合适。它只是不相关记录的集合，那么额外的“封装”事务将是有害的。

小开

如果您先尝试更新然后再插入，竞争条件真的很重要吗？ 假设您有两个线程想要为key关键设置值：

线程1： value=1
线程2：值=2

竞争条件场景示例

  1. 关键未定义
  2. 线程1失败并更新
  3. 线程2更新失败
  4. 线程1或线程2中只有一个成功插入。例如。线程1

  5. 另一个线程失败，插入（错误重复键）-线程2。

    • 结果：要插入的两个踏面中的“第一个”决定价值。
    • 想要的结果：写入数据的2个线程中的最后一个（更新或插入）应该决定值

但是；在多线程环境中，操作系统调度程序决定线程执行的顺序——在上面的场景中，我们有这种竞争条件，是操作系统决定执行的顺序。即：从系统的角度来看，说“线程1”或“线程2”是“第一个”是错误的。

当线程1和线程2的执行时间如此接近时，竞争条件的结果并不重要。唯一的要求应该是其中一个线程应该定义结果值。

对于实现：如果更新后跟插入导致错误“重复键”，则应将其视为成功。

此外，当然不应该假设数据库中的值与您上次写入的值相同。

小开

如果使用UPDATE if-no-rows-更新然后INSERT路由，考虑先执行INSERT以防止竞争条件（假设没有干预DELETE）

INSERT INTO MyTable (Key, FieldA)
SELECT @Key, @FieldA
WHERE NOT EXISTS
(
SELECT *
FROM  MyTable
WHERE Key = @Key
)
IF @@ROWCOUNT = 0
BEGIN
UPDATE MyTable
SET FieldA=@FieldA
WHERE Key=@Key
IF @@ROWCOUNT = 0
... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END

除了避免竞争条件之外，如果在大多数情况下记录已经存在，那么这将导致INSERT失败，浪费CPU。

使用MERGE可能更适合SQL2008以后。

小开

您可以使用此查询。适用于所有SQL服务器版本。它简单明了。但您需要使用2个查询。如果您不能使用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

注：请解释答案是否定的

小开

很多人会建议你使用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

但是，这是有问题的，如果没有其他原因，除了让SQLServer捕获您本可以首先防止的异常之外，成本要高得多，除非在几乎每个插入都失败的极少数情况下。

小开 
/*
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行上的变量设置适当的值（和类型）。

干杯。

小开

我试过下面的解决方案，它适用于我，当并发请求插入语句发生。

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert table (key, ...)
values (@key, ...)
end
commit tran
小开

这取决于使用模式。必须着眼于使用大局而不迷失在细节中。例如，如果创建记录后使用模式是99%的更新，那么'UPSERT'是最佳解决方案。

在第一次插入（命中）之后，它将是所有单个语句更新，没有ifs或buts。插入上的'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`
小开

假设您想插入/更新单行，最理想的方法是使用SQLServer的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）。

小开

您可以使用：

INSERT INTO tableName (...) VALUES (...)
ON DUPLICATE KEY
UPDATE ...

使用它，如果已经有一个特定键的条目，那么它将更新，否则，它将插入。


提交答案