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

Vergessen Sie nicht die Transaktionen. Die Leistung ist gut, aber der einfache Ansatz (IF EXISTS..) ist sehr gefährlich.
Wenn mehrere Threads versuchen, eine Einfügung oder Aktualisierung vorzunehmen, können Sie leicht Primärschlüsselverletzung erhalten.

Die von @Beau Crawford und @Esteban bereitgestellten Lösungen zeigen eine allgemeine Idee, sind aber fehleranfällig.

Um Deadlocks und PK-Verletzungen zu vermeiden, können Sie etwas wie dieses verwenden:

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

oder

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

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

Answer 2

Siehe meine ausführliche Antwort auf eine sehr ähnliche frühere Frage

@Beau Crawford's ist ein guter Weg in SQL 2005 und darunter, obwohl, wenn Sie gewähren rep es sollte auf die gehen der erste Typ, der es SO macht . Das einzige Problem ist, dass es sich bei Einfügungen immer noch um zwei IO-Operationen handelt.

MS Sql2008 stellt vor merge aus dem SQL:2003-Standard:

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

Jetzt ist es wirklich nur eine IO-Operation, aber ein furchtbarer Code :-(

Answer 3

Führen Sie einen UPSERT durch:

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

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

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

Answer 4

Viele Leute werden Ihnen vorschlagen, die MERGE aber ich warne Sie davor. Standardmäßig schützt es Sie nicht vor Gleichzeitigkeit und Race Conditions, genauso wenig wie mehrere Anweisungen, und es birgt andere Gefahren:

• Vorsicht mit der MERGE-Anweisung von SQL Server
• Sie wollen also MERGE verwenden?

Selbst mit dieser "einfacheren" Syntax bevorzuge ich immer noch diesen Ansatz (Fehlerbehandlung der Kürze halber weggelassen):

BEGIN TRANSACTION;

UPDATE dbo.table WITH (UPDLOCK, SERIALIZABLE) 
  SET ... WHERE PK = @PK;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END

COMMIT TRANSACTION;

• Bitte hören Sie auf, dieses UPSERT-Anti-Muster zu verwenden

Viele Leute werden diesen Weg vorschlagen:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
BEGIN
  INSERT ...
END
COMMIT TRANSACTION;

Dies führt jedoch nur dazu, dass Sie die Tabelle möglicherweise zweimal lesen müssen, um die zu aktualisierende(n) Zeile(n) zu finden. Im ersten Beispiel brauchen Sie die Zeile(n) nur einmal zu suchen. (In beiden Fällen wird eine Einfügung vorgenommen, wenn beim ersten Lesen keine Zeile gefunden wird).

Andere werden diesen Weg vorschlagen:

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

Dies ist jedoch problematisch, und zwar aus keinem anderen Grund als dem, dass es viel teurer ist, SQL Server Ausnahmen abfangen zu lassen, die Sie von vornherein hätten verhindern können, außer in dem seltenen Fall, dass fast jede Einfügung fehlschlägt. Ich beweise dies hier:

• Prüfung auf potenzielle Verletzungen von Beschränkungen vor der Eingabe von TRY/CATCH
• Auswirkungen verschiedener Fehlerbehandlungstechniken auf die Leistung

Answer 5

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

Editar:

Leider muss ich, auch zu meinem eigenen Nachteil, zugeben, dass die Lösungen, die dies ohne eine Auswahl tun, besser zu sein scheinen, da sie die Aufgabe mit einem Schritt weniger erledigen.