Lösungen für INSERT OR UPDATE auf SQL Server

Question

Nehmen wir eine Tabellenstruktur von MyTable(KEY, datafield1, datafield2...) .

Häufig möchte ich entweder einen vorhandenen Datensatz aktualisieren oder einen neuen Datensatz einfügen, wenn dieser noch nicht existiert.

Im Wesentlichen:

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

Wie kann man das am besten schreiben?

Answer 1

Wenn Sie mehr als einen Datensatz auf einmal UPSERT möchten, können Sie die ANSI SQL:2003 DML-Anweisung MERGE verwenden.

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

Überprüfen Sie Nachahmung der MERGE-Anweisung in SQL Server 2005 .

Answer 2

Obwohl es ziemlich spät ist, dies zu kommentieren, möchte ich ein vollständigeres Beispiel mit MERGE hinzufügen.

Solche Insert+Update-Anweisungen werden in der Regel als "Upsert"-Anweisungen bezeichnet und können mit MERGE in SQL Server implementiert werden.

Ein sehr gutes Beispiel wird hier gegeben: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Die obigen Ausführungen erklären auch Sperren und Gleichzeitigkeitsszenarien.

Ich werde das Gleiche als Referenz zitieren:

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;

Answer 3

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

Ersetzen Sie Tabellen- und Feldnamen durch die von Ihnen benötigten. Kümmern Sie sich um die mit ON Zustand. Legen Sie dann den entsprechenden Wert (und Typ) für die Variablen in der DECLARE-Zeile fest.

Zum Wohl.

Answer 4

Sie können verwenden MERGE Anweisung, Diese Anweisung wird verwendet, um Daten einzufügen, wenn sie nicht vorhanden sind, oder zu aktualisieren, wenn sie vorhanden sind.

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

Answer 5

Das hängt von der Art der Nutzung ab. Man muss das Gesamtbild der Nutzung betrachten, ohne sich in den Details zu verlieren. Wenn das Nutzungsmuster beispielsweise zu 99 % aus Aktualisierungen besteht, nachdem der Datensatz erstellt wurde, ist "UPSERT" die beste Lösung.

Nach der ersten Einfügung (Treffer) werden alle Aktualisierungen mit einer einzigen Anweisung erfolgen, ohne Wenn und Aber. Die "where"-Bedingung für die Einfügung ist notwendig, da sonst Duplikate eingefügt werden und Sie sich nicht mit Sperren befassen wollen.

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