如何UPSERT（更新或插入）在SQL Server 2005如何UPSERT（更新或插入）在SQL

我有表，其中我将员工，但下一次行，当我想插入一行我不想再次插入该雇员数据只是想和所需的列进行更新，如果它退出，如果有不然后创建新行

我们怎样才能做到这一点在SQL Server 2005？

我使用的JSP

我的查询

String sql="insert into table1(id,name,itemname,itemcatName,itemQty)values('val1','val2','val3','val4','val5')";

如果这是第一次那么，如果存在更新其插入到其他数据库

怎么做？

Answer 1:

尝试检查是否存在：

IF NOT EXISTS (SELECT * FROM dbo.Employee WHERE ID = @SomeID)

    INSERT INTO dbo.Employee(Col1, ..., ColN)
    VALUES(Val1, .., ValN)

ELSE

    UPDATE dbo.Employee
    SET Col1 = Val1, Col2 = Val2, ...., ColN = ValN
    WHERE ID = @SomeID

你可以很容易地把这个包成一个存储过程，只需要调用来自外部存储过程（例如，从编程语言如C＃或任何你正在使用）。

更新：要么你可以只写在一个长字符串这整个语句（可行的-但不是真的非常有用） -或者你可以把它包装成一个存储过程：

CREATE PROCEDURE dbo.InsertOrUpdateEmployee
       @ID INT,
       @Name VARCHAR(50),
       @ItemName VARCHAR(50),  
       @ItemCatName VARCHAR(50),
       @ItemQty DECIMAL(15,2)
AS BEGIN
    IF NOT EXISTS (SELECT * FROM dbo.Table1 WHERE ID = @ID)
       INSERT INTO dbo.Table1(ID, Name, ItemName, ItemCatName, ItemQty)
       VALUES(@ID, @Name, @ItemName, @ItemCatName, @ItemQty)
    ELSE
       UPDATE dbo.Table1
       SET Name = @Name,
           ItemName = @ItemName,
           ItemCatName = @ItemCatName,
           ItemQty = @ItemQty
       WHERE ID = @ID
END

然后只需要调用存储过程从ADO.NET代码

Answer 2:

您可以使用@@ROWCOUNT检查行是否应该被插入或更新：

update table1 
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'
if @@ROWCOUNT = 0
insert into table1(id, name, itemname, itemcatName, itemQty)
values('val1', 'val2', 'val3', 'val4', 'val5')

在这种情况下，如果更新失败，则新行将被插入

Answer 3:

您可以检查是否此行存在，然后插入或更新，但这种保证将要执行两个SQL操作，而不是一个：

检查是否行存在
插入或更新的行

一个更好的解决办法是要始终先更新，如果没有行被更新，然后做一个INSERT，就像这样：

update table1 
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'

if @@ROWCOUNT = 0
  insert into table1(id, name, itemname, itemcatName, itemQty)
  values('val1', 'val2', 'val3', 'val4', 'val5')

这会带领一个SQL操作，或两个SQL操作，这取决于该行是否已经存在。

但是如果性能是一个真正的问题，那么你需要弄清楚如果操作更容易被刀片的或UPDATE的。如果更新的是比较常见的，做以上。如果INSERT的是比较常见的，你可以做反向的，但你必须添加错误处理。

BEGIN TRY
  insert into table1(id, name, itemname, itemcatName, itemQty)
  values('val1', 'val2', 'val3', 'val4', 'val5')
END TRY
BEGIN CATCH
  update table1 
  set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
  where id = 'val1'
END CATCH

为了真正肯定，如果你需要做的更新或插入，你必须在一个事务中做两次手术。从理论上讲，第一个UPDATE或INSERT（甚至EXISTS检查）之后，但在接下来的INSERT / UPDATE语句时，数据库可能已经发生变化，从而导致第二条语句反正失败。这是极为罕见的，而对于交易的开销可能不值得。

或者，你可以使用一个叫做MERGE执行任何一个INSERT或更新一个SQL操作，但也可能是矫枉过正这一个行操作。

考虑阅读有关SQL事务语句，竞争条件， SQL MERGE语句。

Answer 4:

你可以用做INSTEAD OF INSERT触发器在桌子上，来检查该行并取决于它是否已经存在，然后更新/插入是否存在等。还有就是如何做到这一点的SQL Server 2000 + MSDN上的例子在这里：

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate
      FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
      FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END

Answer 5:

下面是关于此事，涵盖了不同的实施模式和反模式有用的文章由迈克尔·斯沃特UPSERT在SQL Server中：
https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/#PerformanceandOtherConcerns

它解决了相关的并发问题（主键冲突，死锁） - 所有在这里没有提供的答案被认为是物品（除了使用触发器@Bridge的解决方案，不覆盖有）的反模式。
下面是与笔者参访溶液中的文章的摘录：

里面一个串行化事务随着锁提示

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRAN

    IF EXISTS ( SELECT * FROM dbo.AccountDetails WITH (UPDLOCK) WHERE Email = @Email )

      UPDATE dbo.AccountDetails
         SET Etc = @Etc
       WHERE Email = @Email;

    ELSE 

      INSERT dbo.AccountDetails ( Email, Etc )
      VALUES ( @Email, @Etc );

  COMMIT

还有与答案在这里对计算器相关的问题： INSERT，UPDATE存储过程SQL Server上

文章来源: How to upsert (update or insert) in SQL Server 2005