Решения для INSERT или UPDATE на SQL Server

Предположим, что структура таблицы MyTable(KEY, datafield1, datafield2...).

Часто я хочу либо обновить существующую запись, либо вставить новую запись, если она не существует.

По существу:

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

Какой лучший способ написать это?

Ответы

Ответ 1

не забывайте о транзакциях. Производительность хорошая, но простой (IF EXISTS..) подход очень опасен.
Когда несколько потоков попытаются выполнить Вставку или обновление, вы можете легко получить нарушение первичного ключа.

Решения, предоставленные @Beau Crawford и @Esteban, показывают общую идею, но подвержены ошибкам.

Чтобы избежать взаимоблокировок и нарушений ПК, вы можете использовать что-то вроде этого:

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

Ответ 2

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

@Beau Crawford's - хороший способ в SQL 2005 и ниже, хотя, если вы предоставляете rep, он должен перейти в первый парень, чтобы он это сделал. Единственная проблема заключается в том, что для вставки он все еще выполняет две операции ввода-вывода.

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, ... )

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

Ответ 3

Сделайте UPSERT:

UPDATE MyTable SET [email protected] WHERE [email protected]

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

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

Ответ 4

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

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;

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

Другие будут предлагать этот способ:

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

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

Ответ 5

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

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

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

Ответ 6

Если вы хотите использовать UPSERT более чем за одну запись за раз, вы можете использовать инструкцию DML ANSI SQL: 2003 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.

Ответ 7

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

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

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

В приведенном выше описании описаны сценарии блокировки и concurrency.

Я буду ссылаться на то же для ссылки:

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;

Ответ 8

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

Приветствия.

Ответ 9

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

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

Ответ 10

В SQL Server 2008 вы можете использовать оператор MERGE

Ответ 11

Если вы перейдете в 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 [email protected]
   WHERE [email protected]
   IF @@ROWCOUNT = 0
   ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END

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

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

Ответ 12

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

Ответ 13

Это зависит от шаблона использования. Нужно смотреть на общую картину использования, не теряясь в деталях. Например, если шаблон использования составляет 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

Ответ 14

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

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

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

Ответ 15

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

Тема 1: value = 1
Тема 2: value = 2

Пример сценария условий гонки

ключ не определен
Ошибка 1 с обновлением
Ошибка 2 с обновлением
Точно один из нити 1 или нити 2 преуспевает со вставкой. Например. поток 1
Другой поток не работает с вставкой (с дубликатом ошибки) - нить 2.
- Результат: "первый" из двух шагов для вставки, определяет значение.
- Требуемый результат: последний из двух потоков для записи данных (обновление или вставка) должен решить значение

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

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

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

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

Ответ 16

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

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

Ответ 17

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

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

Ответ 18

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

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

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

Затем вы:

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

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

Ответ 19

Выполнение if существует... 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')

Ответ 20

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

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

Теперь я не часто следую своим советам, поэтому возьмите его с солью.

Ответ 21

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