Решения для ВСТАВКИ ИЛИ ОБНОВЛЕНИЯ на SQL Server

Сделайте выбор, если получите результат, обновите, если нет, создайте.

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

Редактировать:

Увы, даже в ущерб себе, я должен признать, что решения, которые делают это без выбора, кажутся лучше, поскольку они выполняют задачу с одним шагом меньше.

Сделайте UPSERT:

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

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

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

не забывайте о сделках. Производительность хорошая, но простой (ЕСЛИ СУЩЕСТВУЕТ ..) подход очень опасен. Когда несколько потоков будут пытаться выполнить вставку или обновление, вы можете легко получить нарушение первичного ключа.

Решения, предоставленные @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

Выполнение 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')

Обычно я делаю то, что сказано на некоторых других плакатах относительно того, что сначала проверяю, существует ли он, а затем делаю то, что является правильным путем. Одна вещь, которую вы должны помнить при этом, заключается в том, что план выполнения, кэшированный sql, может быть неоптимальным для того или иного пути. Я считаю, что лучший способ сделать это - вызвать две разные хранимые процедуры.

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

Я не очень часто следую своему собственному совету, так что относитесь к нему с недоверием.

Если вы используете ADO.NET, DataAdapter справится с этим.

Если вы хотите справиться с этим самостоятельно, вот способ:

Убедитесь, что для ключевого столбца существует ограничение первичного ключа.

Затем вы:

1. Сделайте обновление
2. Если обновление не удается из-за того, что запись с ключом уже существует, выполните вставку. Если обновление не завершилось ошибкой, все готово.

Вы также можете сделать это наоборот, то есть сначала выполнить вставку и выполнить обновление, если вставка не удалась. Обычно первый способ лучше, потому что обновления выполняются чаще, чем вставки.

В SQL Server 2008 вы можете использовать оператор 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 ...])

Проверьте Имитация инструкции MERGE в SQL Server 2005.

MS SQL Server 2008 представляет оператор MERGE, который, как я считаю, является частью стандарта SQL: 2003. Как показали многие, обработка случаев с одной строкой не представляет большого труда, но при работе с большими наборами данных необходим курсор со всеми возникающими проблемами производительности. Оператор MERGE будет очень желанным дополнением при работе с большими наборами данных.

Смотрите мой подробный ответ на очень похожий предыдущий вопрос

@ Бо Кроуфорд - хороший способ в SQL 2005 и ниже, хотя, если вы предоставляете репутацию, он должен перейти к первый парень ТАК это. Единственная проблема в том, что для вставок это еще две операции ввода-вывода.

MS Sql2008 представляет merge из стандарта 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, ... )

Теперь это действительно всего одна операция ввода-вывода, но ужасный код :-(

Хотя довольно поздно комментировать это, я хочу добавить более полный пример с использованием MERGE.

Такие операторы Insert + Update обычно называются операторами «Upsert» и могут быть реализованы с помощью MERGE в SQL Server.

Здесь приведен очень хороший пример: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Выше также описаны сценарии блокировки и параллелизма.

Я процитирую то же самое для справки:

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;

Прежде чем все перейдут к HOLDLOCK-s из-за страха перед этими назойливыми пользователями, запускающими ваши sprocs напрямую :-), позвольте мне указать, что Вы должны гарантировать уникальность новых ПК по дизайну (ключи идентификации, генераторы последовательностей в Oracle, уникальные индексы для внешних идентификаторов, запросы, покрытые индексами). Это альфа и омега проблемы. Если у вас его нет, никакие HOLDLOCK-ы вселенной не спасут вас, и если у вас есть это, вам не нужно ничего, кроме UPDLOCK при первом выборе (или для использования обновления сначала).

Sprocs обычно работают в очень контролируемых условиях и с предположением о доверенном вызывающем абоненте (средний уровень). Это означает, что если простой шаблон upsert (обновление + вставка или слияние) когда-либо обнаруживает дублирующийся PK, это означает ошибку в вашем среднем уровне или дизайне таблицы, и хорошо, что SQL в таком случае выдаст ошибку и отклонит запись. Размещение HOLDLOCK в этом случае равносильно поглощению исключений и принятию потенциально ошибочных данных, помимо снижения производительности.

Сказав это, использование MERGE или UPDATE, затем INSERT проще на вашем сервере и менее подвержено ошибкам, поскольку вам не нужно помнить о добавлении (UPDLOCK) для первого выбора. Кроме того, если вы выполняете вставку / обновление небольшими партиями, вам необходимо знать свои данные, чтобы решить, подходит ли транзакция или нет. Если это всего лишь набор несвязанных записей, дополнительная «обволакивающая» транзакция будет вредна.

Имеют ли значение условия гонки, если вы сначала попробуете обновление, а затем вставку? Допустим, у вас есть два потока, которые хотят установить значение для ключа ключ:

Поток 1: значение = 1
Поток 2: значение = 2

Пример сценария состояния гонки

1. ключ не определен
2. Поток 1 не удается обновить
3. Поток 2 не удается обновить
4. Ровно одна из ниток 1 или 2 преуспевает со вставкой. Например. поток 1

5. Другой поток не выполняет вставку (с ключом дублирования ошибки) - поток 2.

   • Результат: «Первая» из двух ступеней, которые нужно вставить, определяет стоимость.
   • Желаемый результат: последний из 2 потоков для записи данных (обновления или вставки) должен определять значение

Но; в многопоточной среде планировщик ОС принимает решение о порядке выполнения потока - в приведенном выше сценарии, где у нас есть это состояние гонки, именно ОС определяла последовательность выполнения. То есть: неправильно говорить, что «поток 1» или «поток 2» был «первым» с точки зрения системы.

Когда время выполнения настолько близко для потока 1 и потока 2, результат состояния гонки не имеет значения. Единственное требование должно заключаться в том, чтобы один из потоков определял результирующее значение.

Для реализации: если обновление, за которым следует вставка, приводит к ошибке «дубликат ключа», это следует рассматривать как успешное.

Кроме того, конечно, никогда не следует предполагать, что значение в базе данных совпадает со значением, которое вы написали последним.

Если вы переходите по маршруту 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

Помимо предотвращения состояния гонки, если в большинстве случаев запись уже существует, это приведет к сбою INSERT, что приводит к потере ресурсов ЦП.

Использование MERGE, вероятно, предпочтительнее для SQL2008 и более поздних версий.

Вы можете использовать этот запрос. Работает во всех редакциях 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

ПРИМЕЧАНИЕ. Объясните, пожалуйста, отрицательные ответы.

Многие люди предложат вам использовать MERGE, но я предостерегаю вас от этого. По умолчанию он не защищает вас от условий параллелизма и гонки не больше, чем несколько операторов, и представляет другие опасности:

• Будьте осторожны с оператором MERGE в SQL Server

Даже при наличии этого «более простого» синтаксиса я все же предпочитаю этот подход (обработка ошибок для краткости опущена):

BEGIN TRANSACTION;

UPDATE dbo.table WITH (UPDLOCK, SERIALIZABLE) 
  SET ... WHERE PK = @PK;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END

COMMIT TRANSACTION;

• Пожалуйста, прекратите использовать этот анти-шаблон UPSERT

Многие предложат такой способ:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

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

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

Другие предложат такой способ:

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

Однако это проблематично, если только по той причине, что позволить SQL Server перехватывать исключения, которые вы могли бы предотвратить в первую очередь, намного дороже, за исключением редкого сценария, когда почти каждая вставка не выполняется. Я доказываю это здесь:

• Проверка возможных нарушений ограничений перед вводом TRY / CATCH
• Влияние различных методов обработки ошибок на производительность

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

Ваше здоровье.

Я пробовал решение ниже, и оно работает для меня, когда возникает одновременный запрос на вставку.

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert table (key, ...)
   values (@key, ...)
end
commit tran

Это зависит от модели использования. Нужно смотреть на общую картину использования, не теряясь в деталях. Например, если шаблон использования - это 99% обновлений после создания записи, то «UPSERT» - лучшее решение.

После первой вставки (попадания) это будут все обновления одного оператора, без каких-либо «если» или «но». Условие «где» для вставки необходимо, иначе она будет вставлять дубликаты, и вы не хотите иметь дело с блокировкой.

UPDATE <tableName> SET <field>=@field WHERE key=@key;

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

Вы можете использовать оператор MERGE. Этот оператор используется для вставки данных, если они не существуют, или обновления, если они существуют.

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

Предполагая, что вы хотите вставить / обновить одну строку, наиболее оптимальный подход - использовать уровень изоляции транзакции REPEATABLE READ SQL Server:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION

    IF (EXISTS (SELECT * FROM myTable WHERE key=@key)
        UPDATE myTable SET ...
        WHERE key=@key
    ELSE
        INSERT INTO myTable (key, ...)
        VALUES (@key, ...)

COMMIT TRANSACTION

Этот уровень изоляции будет предотвращать / блокировать последующие повторяющиеся транзакции чтения от доступа к той же строке (WHERE key=@key), пока текущая транзакция открыта. С другой стороны, операции с другой строкой не будут заблокированы (WHERE key=@key2).