Решения для вставки или обновления на SQL Server

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

@Бо Кроуфорда это хороший способ в 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, ... )

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

сделайте UPSERT:

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

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

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

многие люди будут предлагать вам использовать 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;

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

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

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

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

• http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
• http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

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

Edit:

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

Если вы хотите одновременно вставлять более одной записи, вы можете использовать оператор 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.

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

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

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

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

I будет цитировать то же самое для справки:

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;

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

замените имена таблиц и полей на все, что вам нужно. Позаботьтесь о используя состояние. Затем установите соответствующее значение (и тип) для переменных в строке объявления.

Ура.

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

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

Если вы собираетесь обновить if-no-rows-updated, а затем вставить маршрут, подумайте о том, чтобы сначала вставить, чтобы предотвратить состояние гонки (предполагая, что нет промежуточного удаления)

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

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

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

в SQL Server 2008 можно использовать инструкцию MERGE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Я пробовал ниже решение, и оно работает для меня, когда происходит одновременный запрос инструкции 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

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

Примечание: пожалуйста, объясните отрицательные ответы

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

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

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

затем:

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

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

выполнение if существует ... еще... включает в себя выполнение минимум двух запросов (один для проверки, один для принятия мер). Следующий подход требует только один, где запись существует, два, если требуется вставка:

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)

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