sql insert多行 insert语法 - SQL Server上的INSERT OR UPDATE解决方案




11 Answers

不要忘记交易。 性能很好,但简单(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
sql修改数据 sql新增记录

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

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

主要有:

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

什么是写这个最好的表现方式?







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

编辑:

唉,即使对我自己不利,我必须承认,这样做的解决方案没有选择似乎更好,因为他们以较少的步骤完成任务。




尽管对此很晚评论,但我想使用MERGE添加更完整的示例。

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

这里给出了一个非常好的例子: weblogs.sqlteam.com/dang/archive/2009/01/31/… : weblogs.sqlteam.com/dang/archive/2009/01/31/…

上面解释了锁定和并发情况。

我将引用相同的参考文献:

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语句,如果不存在,则使用此语句插入数据,如果存在则更新。

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



如果进行更新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 [email protected]
   WHERE [email protected]
   IF @@ROWCOUNT = 0
   ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END

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

使用MERGE可能更适合SQL2008以上版本。




在所有人都直接跳到HOLDLOCK-s之前,这些用户直接运行你的sprocs :-)让我指出你必须保证新设计的PK-s的独特性 (身份证,Oracle中的序列生成器,外部ID-s,索引覆盖的查询)。 这是问题的阿尔法和欧米茄。 如果你没有,那么宇宙的HOLDLOCK-s就不会拯救你,如果你有这个,那么在第一次选择时(或者首先使用更新)你不需要超越UPDLOCK的任何东西。

Sprocs通常在非常受控制的条件下运行,并假设可信来电者(中间层)。 这意味着如果一个简单的upsert模式(update + insert或merge)看到重复的PK,这意味着您的中间层或表设计中存在一个错误,那么SQL会在这种情况下大声指出错误并拒绝该记录。 在这种情况下放置一个HOLDLOCK等于除了减少你的性能之外,还会吃异常并吸收潜在的错误数据。

话虽如此,使用MERGE或UPDATE,然后INSERT在您的服务器上更容易,并且因为您不必记住添加(UPDLOCK)到第一次选择,所以出错率更低。 另外,如果您要小批量插入/更新,则需要知道数据以确定事务是否合适。 它只是一个无关记录的集合,那么额外的“包络”交易将是有害的。




这取决于使用模式。 一个人不得不在细节中看到用法大图。 例如,如果创建记录后使用模式为99%更新,那么'UPSERT'是最佳解决方案。

在第一次插入(命中)之后,它将是所有单个语句更新,不是ifs或buts。 插入的'where'条件是必要的,否则它会插入重复项,并且你不想处理锁定。

UPDATE <tableName> SET <field>[email protected] WHERE [email protected];

IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO <tableName> (field)
   SELECT @field
   WHERE NOT EXISTS (select * from tableName where key = @key);
END



如果您使用ADO.NET,则DataAdapter将处理此问题。

如果你想自己处理它,就是这样的:

确保您的密钥列上存在主键约束。

然后你:

  1. 做更新
  2. 如果由于具有密钥的记录已存在而导致更新失败,请执行插入操作。 如果更新没有失败,则完成。

您也可以反过来做,也就是先插入,然后在插入失败时进行更新。 通常情况下,第一种方式更好,因为更新操作比插入操作更频繁。




做一个if if exists ... else ...涉及到两个请求最小(一个检查,一个采取行动)。 以下方法只需要一个存在记录的地方,如果需要插入则需要两个:

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



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




Related