SQL Server 上 INSERT OR UPDATE 的解决方案

Solutions for INSERT OR UPDATE on SQL Server

提问by Chris Cudmore

Assume a table structure of MyTable(KEY, datafield1, datafield2...).

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

Often I want to either update an existing record, or insert a new record if it doesn't exist.

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

Essentially:

本质上：

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

What's the best performing way to write this?

写这个的最佳表现方式是什么？

采纳答案by aku

don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.
When multiple threads will try to perform Insert-or-update you can easily get primary key violation.

不要忘记交易。性能很好，但是简单的（IF EXISTS..）方法是非常危险的。
当多个线程尝试执行插入或更新时，您很容易违反主键。

Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

@Beau Crawford 和 @Esteban 提供的解决方案展示了总体思路，但容易出错。

To avoid deadlocks and PK violations you can use something like this:

为了避免死锁和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

回答by Keith

See my detailed answer to a very similar previous question

请参阅我对之前一个非常相似的问题的详细回答

@Beau Crawford'sis a good way in SQL 2005 and below, though if you're granting rep it should go to the first guy to SO it. The only problem is that for inserts it's still two IO operations.

@Beau Crawford's在 SQL 2005 及更低版本中是一个好方法，但如果您授予代表权，它应该交给第一个 SO it 的人。唯一的问题是对于插入，它仍然是两个 IO 操作。

MS Sql2008 introduces mergefrom the SQL:2003 standard:

MS Sql2008merge从 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, ... )

Now it's really just one IO operation, but awful code :-(

现在它真的只是一个 IO 操作，但是代码很糟糕:-(

回答by Beau Crawford

Do an UPSERT:

做一个UPSERT：

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

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

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

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

回答by Aaron Bertrand

Many people will suggest you use MERGE, but I caution you against it. By default, it doesn't protect you from concurrency and race conditions any more than multiple statements, but it does introduce other dangers:

许多人会建议您使用MERGE，但我警告您不要这样做。默认情况下，它不会像多个语句一样保护您免受并发和竞争条件的影响，但它确实引入了其他危险：

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Even with this "simpler" syntax available, I still prefer this approach (error handling omitted for brevity):

即使有了这种“更简单”的语法，我仍然更喜欢这种方法（为简洁起见，省略了错误处理）：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;

A lot of folks will suggest this way:

很多人会建议这样：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
  INSERT ...
END
COMMIT TRANSACTION;

But all this accomplishes is ensuring you may need to read the table twice to locate the row(s) to be updated. In the first sample, you will only ever need to locate the row(s) once. (In both cases, if no rows are found from the initial read, an insert occurs.)

但所有这些都确保您可能需要读取表两次才能找到要更新的行。在第一个示例中，您只需要定位行一次。（在这两种情况下，如果没有从初始读取中找到行，就会发生插入。）

Others will suggest this way:

其他人会这样建议：

BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH

However, this is problematic if for no other reason than letting SQL Server catch exceptions that you could have prevented in the first place is much more expensive, except in the rare scenario where almost every insert fails. I prove as much here:

但是，如果除了让 SQL Server 捕获您一开始可以阻止的异常更昂贵之外，没有其他原因，这就会有问题，除非在几乎每个插入都失败的罕见情况下。我在这里证明：

• http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
• http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

• http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
• http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

回答by Esteban Araya

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

Edit:

编辑：

Alas, even to my own detriment, I must admit the solutions that do this without a select seem to be better since they accomplish the task with one less step.

唉，即使对我自己不利，我也必须承认，在没有选择的情况下执行此操作的解决方案似乎更好，因为它们只需少一步即可完成任务。

回答by Eric Weilnau

If you want to UPSERT more than one record at a time you can use the ANSI SQL:2003 DML statement MERGE.

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

Check out Mimicking MERGE Statement in SQL Server 2005.

查看在 SQL Server 2005 中模拟 MERGE 语句。

回答by user243131

Although its pretty late to comment on this I want to add a more complete example using MERGE.

尽管对此发表评论为时已晚，但我想使用 MERGE 添加一个更完整的示例。

Such Insert+Update statements are usually called "Upsert" statements and can be implemented using MERGE in SQL Server.

这样的 Insert+Update 语句通常称为“Upsert”语句，可以在 SQL Server 中使用 MERGE 来实现。

A very good example is given here: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

这里给出了一个很好的例子：http: //weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

The above explains locking and concurrency scenarios as well.

上面也解释了锁定和并发场景。

I will be quoting the same for reference:

我将引用相同的内容以供参考：

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;

回答by Denver

/*
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

Replace table and field names by whatever you need. Take care of the using ONcondition. Then set the appropriate value (and type) for the variables on the DECLARE line.

用您需要的任何名称替换表和字段名称。注意使用 ON条件。然后为 DECLARE 行上的变量设置适当的值（和类型）。

Cheers.

干杯。

回答by Daniel Acosta

You can use MERGEStatement, This statement is used to insert data if not exist or update if does exist.

您可以使用MERGEStatement，此语句用于在不存在时插入数据或在存在时更新。

MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

回答by Kristen

If going the UPDATE if-no-rows-updated then INSERT route, consider doing the INSERT first to prevent a race condition (assuming no intervening DELETE)

如果使用 UPDATE if-no-rows-updated 然后 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

Apart from avoiding a race condition, if in most cases the record will already exist then this will cause the INSERT to fail, wasting CPU.

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

Using MERGE probably preferable for SQL2008 onwards.

从 SQL2008 开始，使用 MERGE 可能更可取。