Как обновить (обновить или вставить) в SQL Server 2005

Ответ 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

Вы можете легко обернуть это в хранимую процедуру и просто вызвать эту хранимую процедуру извне (например, из языка программирования, такого как С# или из того, что вы используете).

Обновление: либо вы можете просто написать весь этот оператор в одну длинную строку (выполнимо - но на самом деле не очень полезно) - либо вы можете обернуть его в хранимую процедуру:

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

Ответ 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')

в этом случае, если обновление не выполнено, новая строка будет вставлена ​​

Ответ 3

Вы можете проверить, существует ли строка, а затем INSERT или UPDATE, но это гарантирует, что вы будете выполнять две операции SQL вместо одной:

1. проверьте, существует ли строка
2. вставить или обновить строку

Лучшее решение - сначала всегда ОБНОВЛЯТЬ, и если строки не были обновлены, тогда сделать 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, в зависимости от того, существует ли уже строка.

Но если производительность действительно является проблемой, то вам нужно выяснить, являются ли операции более вероятными INSERT или 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, база данных могла измениться, что в любом случае привело к сбою второго оператора. Это чрезвычайно редко, и накладные расходы на транзакции могут не стоить этого.

С другой стороны, вы можете использовать одну операцию SQL под названием MERGE для выполнения INSERT или UPDATE, но это также, вероятно, излишне для этой однострочной операции.

Подумайте о прочтении операторов SQL-транзакций, условий гонки, оператора SQL MERGE.

Ответ 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

Ответ 5

Вот полезная статья Майкла Дж. Сварта по этому вопросу, которая охватывает различные шаблоны и антишаблоны для реализации UPSERT в SQL Server:
https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

В нем рассматриваются связанные с этим проблемы параллелизма (нарушения первичного ключа, взаимоблокировки) - все ответы, представленные здесь, пока рассматриваются как антипаттерны в статье (за исключением решения @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

Здесь также есть связанный вопрос с ответами на stackoverflow: Вставить обновление хранимого процесса на SQL Server.