Rozwiązania do INSERT lub UPDATE na SQL Server

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

@Beau Crawford ' s jest dobrym sposobem w SQL 2005 i poniżej, choć jeśli przyznajesz rep powinien iść do pierwszy facet, który tak to . Jedynym problemem jest to, że dla wkładek to jeszcze dwie operacje IO.

MS SQL2008 wprowadza merge ze 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:- (

Zrób UPSERT:

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

IF @@ROWCOUNT = 0
   INSERT INTO MyTable (FieldA) VALUES (@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ścigu tak samo jak wiele instrukcji, ale wprowadza inne zagrożenia:

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

Nawet z tą "prostszą" składnią, nadal preferuję takie podejście (pomijanie obsługi błędów dla zwięzłości):

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;

Ale wszystko to sprawia, że może być konieczne dwukrotne przeczytanie tabeli, aby zlokalizować wiersz(y) do aktualizacji. W pierwszej próbce wystarczy tylko raz zlokalizować wiersz(y). (W obu przypadkach, jeśli nie znaleziono wierszy od początkowego odczytu, następuje wstawka.)

Inni będą sugerować w ten sposób:

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

Jest to jednak problematyczne, jeśli nie z innego powodu niż pozwolenie SQL Server na wyłapywanie WYJĄTKÓW, którym można było zapobiec w pierwszej kolejności, jest znacznie droższe, z wyjątkiem rzadkiego scenariusza, w którym prawie każda wkładka zawodzi. Udowadniam tu tyle:

• 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:

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

Jeśli chcesz UPSERTOWAĆ 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 skomentować to chcę dodać bardziej kompletny przykład za pomocą MERGE.

Takie instrukcje Insert+Update są zwykle nazywane instrukcjami "Upsert" i mogą być zaimplementowane za pomocą MERGE w SQL Server.

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

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

Będę cytował to samo dla odniesienie:

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. Zadbaj o stan using ON. Następnie ustaw odpowiednią wartość (i typ) dla zmiennych w linii DECLARE.

Można użyć instrukcji MERGE, Ta instrukcja służy do wstawiania danych, jeśli nie istnieją lub aktualizacji, jeśli istnieją.

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

Jeśli będzie aktualizacja if-no-rows - updated to Wstaw trasę, rozważ najpierw wstawienie, aby zapobiec Warunkom wyścigu (zakładając, że nie interweniuje 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 unikania warunku wyścigowego, jeśli w większości przypadków rekord już istnieje, spowoduje to awarię wkładki, marnując procesor.

Używanie MERGE jest prawdopodobnie lepsze dla sql2008.

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

To zależy od wzorca użycia. Trzeba spojrzeć na wykorzystanie big picture bez zagubienia się w szczegółach. Na przykład, jeśli wzorzec użycia jest aktualizowany w 99% po utworzeniu rekordu, to "UPSERT" jest najlepszym rozwiązaniem.

Po pierwszym wstawieniu (trafieniu), będą to wszystkie aktualizacje pojedynczych instrukcji, bez IFS lub ale. Warunek "gdzie" na wkładce jest konieczny, w przeciwnym razie wstawia 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 polecenie MERGE, które moim zdaniem jest częścią standardu SQL:2003. Jak wielu pokazało, nie jest to wielka sprawa, aby obsłużyć jeden wiersz przypadków, ale w przypadku dużych zbiorów danych, trzeba kursor, ze wszystkimi problemami wydajności, które pojawiają się. Instrukcja MERGE będzie bardzo mile widzianym dodatkiem w przypadku dużych zbiorów danych.

Zanim wszyscy przeskoczą do HOLDLOCK-s ze strachu przed tymi nafaszerowanymi użytkownikami, którzy bezpośrednio uruchamiają Twoje sprocks: -) pozwól, że zwrócę uwagę, że musisz zagwarantować wyjątkowość nowych PK-S według projektu (klucze tożsamości, Generatory sekwencji w Oracle, unikalne indeksy dla zewnętrznych ID-s, zapytania objęte indeksami). To jest alfa i omega problemu. Jeśli nie masz tego, żaden blokada wszechświata cię nie uratuje, a jeśli masz to, nie potrzebujesz niczego poza UPDLOCK na pierwszym wybierz (lub najpierw użyj aktualizacji).

Koła zębate normalnie działają w bardzo kontrolowanych warunkach i z założeniem zaufanego rozmówcy (mid tier). Oznacza to, że jeśli prosty wzorzec upsert (update+insert lub merge) kiedykolwiek zobaczy duplikat PK, oznacza to błąd w projekcie średniej warstwy lub tabeli i dobrze, że SQL wykrzyczy błąd w takim przypadku i odrzuci rekord. Umieszczenie blokady w tym przypadku równa się zjadaniu wyjątków i przyjmowaniu potencjalnie wadliwych danych, poza zmniejszeniem perf.

Powiedziawszy, że używanie MERGE, lub UPDATE then INSERT jest łatwiejsze na twoim serwerze i mniej podatne na błędy, ponieważ nie musisz pamiętać, aby dodać (UPDLOCK), aby najpierw wybrać. Ponadto, jeśli robisz wstawianie / aktualizacje w małych partiach, musisz znać swoje dane, aby zdecydować, czy transakcja jest odpowiednia, czy nie. To tylko zbiór niepowiązanych rekordów, wtedy dodatkowa transakcja "kopertowania" 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 :

Thread 1: value = 1
Thread 2: value = 2

Przykładowy scenariusz rasy

1. Klucz nie jest zdefiniowany
2. Thread 1 fails with update
3. Thread 2 fails with update
4. dokładnie jeden z wątków 1 lub wątek 2 kończy się wstawką. Np. wątek 1

5. Drugi wątek nie działa z insert (z błędnym klawiszem duplicate) - wątek 2.

   • wynik:" pierwszy " z dwóch stopni do wstawienia decyduje o wartości.
   • Wanted result: ostatni z 2 wątków do zapisu danych (update lub insert) powinien decydować o wartości

Ale; w środowisku wielowątkowym, OS scheduler decyduje o kolejności wykonania wątku - w powyższym scenariuszu, gdzie mamy ten warunek wyścigu, to OS, który zadecydował o kolejności egzekucji. Ie: błędem jest stwierdzenie, że "wątek 1" lub " wątek 2 "był" pierwszy " z punktu widzenia systemu.

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

Dla implementacji: jeśli aktualizacja, po której następuje insert, spowoduje błąd "duplicate key", powinno to być traktowane jako sukces.

Także, nie należy oczywiście zakładać, że wartość w bazie danych jest taka sama jak wartość, którą ostatnio napisałeś.

Próbowałem poniżej rozwiązania i działa dla mnie, gdy występuje równoczesne żądanie polecenia 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 edycjach 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ć negatywy odpowiedzi

Jeśli używasz ADO.NET, DataAdapter zajmuje się tym.

Jeśli chcesz to załatwić sam, to tak:

Upewnij się, że w kolumnie klucza znajduje się ograniczenie klucza głównego.

Wtedy ty:

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

Można też zrobić to na odwrót, tzn. najpierw zrobić insert, a potem update jeśli insert nie powiodło się. Zwykle pierwszy sposób jest lepszy, ponieważ aktualizacje są wykonywane częściej niż wstawki.

Sprawdzanie, czy istnieje ... else ... wymaga wykonania minimum dwóch żądań (jeden do sprawdzenia, jeden do podjęcia działań). Następujące podejście wymaga tylko jednego, jeśli rekord istnieje, 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')

Zwykle robię to, o czym mówiło kilka innych plakatów, w odniesieniu do sprawdzenia, czy istnieje, a następnie robię cokolwiek, co jest właściwą ścieżką. Jedną z rzeczy, o których powinieneś 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:
If Exists
   Call SecondSP (UpdateProc)
Else
   Call ThirdSP (InsertProc)

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