Rozwiązania dla INSERT OR UPDATE na SQL Server

Załóżmy strukturę tabeli MyTable(KEY, datafield1, datafield2...).

Często chcę zaktualizować istniejący rekord lub wstawić nowy, jeśli nie istnieje.

Głównie:

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

Jak najlepiej to napisać?

nie zapomnij o transakcjach. Wydajność jest dobra, ale proste (JEŚLI ISTNIEJE) podejście jest bardzo niebezpieczne.
Gdy wiele wątków będzie próbowało wykonać wstawianie lub aktualizację, możesz łatwo uzyskać naruszenie klucza podstawowego.

Rozwiązania dostarczone przez @Beau Crawford i @Esteban pokazują ogólny pomysł, ale są podatne na błędy.

Aby uniknąć zakleszczeń i naruszeń PK, możesz użyć czegoś takiego:

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

lub

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

Zobacz moją szczegółową odpowiedź na bardzo podobne poprzednie pytanie

@Beau Crawford's jest dobrym sposobem w SQL 2005 i niższych, ale jeśli udzielasz rep, powinien przejść do pierwszego faceta, który go SO . Jedynym problemem jest to, że dla wstawek są to nadal dwie operacje IO.

MS Sql2008 wprowadza mergeze standardu 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, ... )

Teraz to naprawdę tylko jedna operacja IO, ale okropny kod :-(

Wykonaj UPSERT:

AKTUALIZACJA MyTable SET FieldA = @ FieldA WHERE Key = @ Key

IF @@ ROWCOUNT = 0
   WSTAWIĆ DO WARTOŚCI MyTable (FieldA) (@FieldA)

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

Wiele osób zasugeruje ci użycie MERGE, ale ostrzegam cię przed tym. Domyślnie nie chroni cię przed współbieżnością i warunkami wyścigowymi więcej niż wiele oświadczeń, ale wprowadza inne niebezpieczeństwa:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Nawet przy tej dostępnej „prostszej” składni nadal wolę to podejście (pominięcie obsługi błędów ze względu na zwięzłość):

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;

Wielu ludzi zasugeruje w ten sposób:

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;

Ale to wszystko zapewnia, że ​​konieczne może być dwukrotne przeczytanie tabeli w celu zlokalizowania wierszy, które mają zostać zaktualizowane. W pierwszej próbce będziesz musiał tylko zlokalizować rząd (wiersze) tylko raz. (W obu przypadkach, jeśli nie zostaną znalezione wiersze z początkowego odczytu, nastąpi wstawienie).

Inni sugerują w ten sposób:

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

Jest to jednak problematyczne, jeśli tylko z innego powodu niż zezwolenie SQL Serverowi na wychwytywanie wyjątków, którym można było zapobiec, jest znacznie droższe, z wyjątkiem rzadkiego scenariusza, w którym prawie każda wstawka ulega awarii. Udowadniam tutaj tyle samo:

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

Edytować:

Niestety, nawet na własną szkodę, muszę przyznać, że rozwiązania, które dokonują tego bez wyboru, wydają się lepsze, ponieważ wykonują zadanie o jeden krok mniej.

Jeśli chcesz przesłać więcej niż jeden rekord na raz, możesz użyć instrukcji 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 ...])

Sprawdź naśladowanie instrukcji MERGE w SQL Server 2005 .

Chociaż jest dość późno, aby to skomentować, chcę dodać bardziej kompletny przykład za pomocą MERGE.

Takie instrukcje Insert + Update są zwykle nazywane instrukcjami „Upsert” i można je zaimplementować za pomocą funkcji MERGE w programie SQL Server.

Bardzo dobry przykład podano tutaj: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Powyżej wyjaśnia również scenariusze blokowania i współbieżności.

Będę cytować to samo w celach informacyjnych:

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

Zamień nazwy tabel i pól na dowolne potrzebne. Dbać o stosowania na stanie. Następnie ustaw odpowiednią wartość (i typ) dla zmiennych w wierszu DECLARE.

Twoje zdrowie.

Można użyć MERGEinstrukcji, instrukcja służy do wstawiania danych, jeśli nie istnieje, lub aktualizacji, jeśli istnieje.

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

W przypadku aktualizacji UPDATE, jeśli nie zaktualizowano wierszy, a następnie INSERT, należy najpierw rozważyć wprowadzenie instrukcji INSERT, aby zapobiec warunkom wyścigu (zakładając, że nie ma potrzeby usuwania USUŃ)

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

Oprócz uniknięcia wyścigu, jeśli w większości przypadków rekord już istnieje, spowoduje to awarię INSERT, marnując procesor.

Używanie MERGE prawdopodobnie jest lepsze dla SQL2008 i późniejszych.

To zależy od wzorca użytkowania. Trzeba patrzeć na duży obraz użytkowania bez zagubienia się w szczegółach. Na przykład, jeśli wzorzec użycia to 99% aktualizacji po utworzeniu rekordu, najlepszym rozwiązaniem jest „UPSERT”.

Po pierwszym wstawieniu (trafieniu) będą to wszystkie aktualizacje pojedynczej instrukcji, bez ifs lub ale. Warunek „gdzie” na wkładce jest konieczny, w przeciwnym razie wstawi duplikaty i nie chcesz zajmować się blokowaniem.

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 wprowadza instrukcję MERGE, która moim zdaniem jest częścią standardu SQL: 2003. Jak wielu pokazało, nie jest wielką sprawą zajmowanie się przypadkami z jednym wierszem, ale w przypadku dużych zestawów danych potrzebny jest kursor wraz ze wszystkimi problemami z wydajnością. Instrukcja MERGE będzie mile widzianym dodatkiem w przypadku dużych zestawów danych.

Zanim wszyscy przejdą do HOLDLOCK-ów ze strachu przed tymi okropnymi użytkownikami, którzy bezpośrednio uruchamiają twoje sproki :-) pozwól mi zaznaczyć, że musisz zagwarantować unikalność nowych PK-ów według projektu (klucze tożsamości, generatory sekwencji w Oracle, unikalne indeksy dla zewnętrzne identyfikatory, zapytania objęte indeksami). To jest alfa i omega problemu. Jeśli tego nie masz, żadne HOLDLOCK-y wszechświata cię nie uratują, a jeśli tak, to nie potrzebujesz niczego poza UPDLOCK przy pierwszym wyborze (lub najpierw użyj aktualizacji).

Sprocs zwykle działają w ściśle kontrolowanych warunkach i przy założeniu, że osoba dzwoniąca jest zaufana (poziom środkowy). Oznacza to, że jeśli prosty wzorzec wstawiania (aktualizacja + wstawianie lub scalanie) kiedykolwiek zobaczy duplikat PK, oznacza to błąd w projekcie warstwy pośredniej lub tabeli i dobrze, że SQL wykrzyknie błąd w takim przypadku i odrzuci rekord. Umieszczenie HOLDLOCK w tym przypadku jest równoznaczne z jedzeniem wyjątków i przyjmowaniem potencjalnie błędnych danych, oprócz zmniejszenia wydajności.

Powiedziawszy to, używając MERGE lub UPDATE, INSERT jest łatwiejszy na twoim serwerze i mniej podatny na błędy, ponieważ nie musisz pamiętać, aby dodać (UPDLOCK), aby najpierw wybrać. Ponadto, jeśli robisz wstawki / aktualizacje w małych partiach, musisz znać swoje dane, aby zdecydować, czy transakcja jest odpowiednia, czy nie. Jest to tylko zbiór niepowiązanych zapisów, a wówczas dodatkowa transakcja „otaczania” będzie szkodliwa.

Czy warunki wyścigu naprawdę mają znaczenie, jeśli najpierw spróbujesz aktualizacji, a następnie wstawki? Powiedzmy, że masz dwa wątki, które chcą ustawić wartość dla klucza klucza :

Wątek 1: wartość = 1
Wątek 2: wartość = 2

Przykładowy scenariusz wyścigu

1. klucz nie jest zdefiniowany
2. Aktualizacja 1 nie powiedzie się
3. Aktualizacja 2 nie powiedzie się
4. Dokładnie jeden z wątku 1 lub wątku 2 kończy się powodzeniem. Np. Wątek 1

5. Drugi wątek kończy się niepowodzeniem przy wstawianiu (z błędnym duplikatem klucza) - wątek 2.

   • Wynik: „Pierwszy” z dwóch stopni do wstawienia decyduje o wartości.
   • Pożądany wynik: ostatni z 2 wątków do zapisu danych (aktualizacja lub wstawienie) powinien zdecydować o wartości

Ale; w środowisku wielowątkowym harmonogram systemu operacyjnego decyduje o kolejności wykonania wątku - w powyższym scenariuszu, w którym mamy ten warunek wyścigu, to system operacyjny zdecydował o kolejności wykonania. Tzn .: Błędem jest twierdzenie, że „wątek 1” lub „wątek 2” był „pierwszy” z systemowego punktu widzenia.

Kiedy czas wykonania jest tak blisko dla wątku 1 i wątku 2, wynik warunków wyścigu nie ma znaczenia. Jedynym wymaganiem powinno być, aby jeden z wątków zdefiniował wynikową wartość.

Do wdrożenia: jeśli aktualizacja, po której następuje wstawienie, powoduje błąd „duplikowanie klucza”, należy to traktować jako sukces.

Oczywiście nie należy oczywiście zakładać, że wartość w bazie danych jest taka sama, jak wartość, którą zapisałeś jako ostatni.

W SQL Server 2008 można użyć instrukcji MERGE

Wypróbowałem poniższe rozwiązanie i działa ono dla mnie, gdy pojawia się współbieżne żądanie instrukcji 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

Możesz użyć tego zapytania. Praca we wszystkich wersjach SQL Server. To proste i jasne. Ale potrzebujesz użyć 2 zapytań. Możesz użyć, jeśli nie możesz użyć 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

UWAGA: Proszę wyjaśnić odpowiedzi przeczące

Jeśli używasz ADO.NET, DataAdapter obsługuje to.

Jeśli chcesz sobie z tym poradzić, jest to następujący sposób:

Upewnij się, że kolumna klucza zawiera ograniczenie klucza podstawowego.

Więc ty:

1. Wykonaj aktualizację
2. Jeśli aktualizacja się nie powiedzie, ponieważ rekord z kluczem już istnieje, wykonaj operację wstawiania. Jeśli aktualizacja się nie powiedzie, jesteś skończony.

Możesz to również zrobić w drugą stronę, tj. Najpierw wstawić i wykonać aktualizację, jeśli wstawienie się nie powiedzie. Zwykle pierwszy sposób jest lepszy, ponieważ aktualizacje są wykonywane częściej niż wstawki.

Wykonanie if istnieje ... w przeciwnym razie ... wymaga wykonania co najmniej dwóch żądań (jednego do sprawdzenia, drugiego do podjęcia działania). Poniższe podejście wymaga tylko jednego, w którym istnieje rekord, dwóch, jeśli wymagana jest wstawka:

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')

Zazwyczaj robię to, co powiedziało kilka innych plakatów, odnosząc się najpierw do sprawdzenia, czy istnieje, a potem robię wszystko, co jest właściwe. Jedną z rzeczy, o których należy pamiętać, jest to, że plan wykonania buforowany przez sql może być nieoptymalny dla jednej lub drugiej ścieżki. Uważam, że najlepszym sposobem na to jest wywołanie dwóch różnych procedur składowanych.

FirstSP:
Jeśli istnieje
   Zadzwoń SecondSP (UpdateProc)
Jeszcze
   Zadzwoń do ThirdSP (InsertProc)

Teraz nie stosuję się często do moich rad, więc weź to z odrobiną soli.

Wybierz, jeśli otrzymasz wynik, zaktualizuj go, jeśli nie, utwórz go.