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




insert语法 sql修改数据 (18)

我通常会做其他海报中的其他几位海报,先检查它是否存在,然后采取正确的道路。 在做这件事时你应该记住的一件事是,由sql缓存的执行计划对于一个路径或其他路径可能不是最佳的。 我相信最好的方法是调用两个不同的存储过程。

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

现在,我并不经常遵循我自己的建议,所以拿一粒盐来。

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

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

主要有:

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

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


如果您首先尝试更新后再插入,竞争条件是否真的很重要? 假设你有两个线程想要为键值设置一个值:

线程1:值= 1
线程2:值= 2

示例竞赛条件情况

  1. 未定义
  2. 线程1失败并更新
  3. 线程2失败并更新
  4. 线程1或线程2中的一个成功插入。 例如线程1
  5. 另一个线程失败,插入(带有错误重复键) - 线程2。

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

但; 在多线程环境中,操作系统调度程序决定线程执行的顺序 - 在上面的场景中,我们有这种竞争条件,它是决定执行顺序的操作系统。 即:从系统的观点来看,“线程1”或“线程2”是“第一”是错误的。

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

对于实现:如果更新后再插入导致错误“重复密钥”,则应视为成功。

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


看到我对前面一个非常类似的问题的详细解答

@Beau Crawford's在SQL 2005及以下版本中是一个好方法,但如果您授予代表,则应该让第一个人参与 。 唯一的问题是插入它仍然是两个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操作,但代码非常糟糕:-(


/*
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行上为变量设置适当的值(和类型)。

干杯。


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

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

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

然后你:

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

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


如果您想要一次写入多条记录,则可以使用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 ...])

在SQL Server 2005中检查模仿MERGE语句


在所有人都直接跳到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

在SQL Server 2008中,您可以使用MERGE语句


许多人会建议你使用MERGE ,但我警告你不MERGE 。 默认情况下,它不会保护您不受并发和竞争条件的影响,而是会引入其他危险:

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

即使使用这个“更简单”的语法,我仍然更喜欢这种方法(为简洁起见省略了错误处理):

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;

很多人会这样建议:

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;

但是,所有这些都是为了确保您可能需要读取表格两次以找到要更新的行。 在第一个示例中,您只需要定位行(s)一次。 (在这两种情况下,如果从初始读取中未找到行,则会发生插入。)

其他人会这样建议:

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

但是,如果没有其他原因,让SQL Server捕获可能首先阻止的异常更为昂贵,除非几乎每个插入失败的罕见情况中,否则这是有问题的。 我在这里证明了这一点:


不要忘记交易。 性能很好,但简单(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

MS SQL Server 2008引入了MERGE语句,我相信它是SQL:2003标准的一部分。 正如许多人已经表明处理一个行情况并不是什么大不了的事,但是在处理大型数据集时,需要一个光标,并带有所有的性能问题。 在处理大型数据集时,MERGE语句将受到欢迎。


您可以使用MERGE语句,如果不存在,则使用此语句插入数据,如果存在则更新。

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


尽管对此很晚评论,但我想使用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;

你可以使用这个查询。 适用于所有SQL Server版本。 这很简单,清楚。 但是你需要使用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

注意:请解释回答否定


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

编辑:

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


如果您使用MySQL而不是SQL Server,则语法为:

UPDATE Table1
INNER JOIN Table2
ON Table1.id = Table2.id
SET Table1.col1 = Table2.col1,
    Table1.col2 = Table2.col2






sql sql-server database insert upsert