Hoe upsert (bijwerken of invoegen) in SQL Server 2005

Ik heb een tabel waarin ik rijen voor een werknemer invoeg, maar de volgende keer dat ik een rij wil invoegen, wil ik niet opnieuw gegevens voor die werknemer invoegen. Ik wil alleen de vereiste kolommen bijwerken als het daar wordt afgesloten, zo niet, maak dan nieuwe rij

Hoe kunnen we dit doen in SQL Server 2005?

Ik gebruik jsp

mijn vraag is

String sql="insert into table1(id,name,itemname,itemcatName,itemQty)values('val1','val2','val3','val4','val5')";

als het de eerste keer is, plaats het dan in de database, anders als het bestaat, update het

hoe te doen?

Antwoord 1, autoriteit 100%

Probeer het bestaan te controleren:

IF NOT EXISTS (SELECT * FROM dbo.Employee WHERE ID = @SomeID)
    INSERT INTO dbo.Employee(Col1, ..., ColN)
    VALUES(Val1, .., ValN)
ELSE
    UPDATE dbo.Employee
    SET Col1 = Val1, Col2 = Val2, ...., ColN = ValN
    WHERE ID = @SomeID

Je zou dit gemakkelijk in een opgeslagen procedure kunnen stoppen en die opgeslagen procedure gewoon van buitenaf kunnen aanroepen (bijvoorbeeld vanuit een programmeertaal zoals C# of wat je ook gebruikt).

Update:ofwel kun je deze hele instructie gewoon in één lange reeks schrijven (uitvoerbaar – maar niet echt handig) – of je kunt het in een opgeslagen procedure inpakken:

CREATE PROCEDURE dbo.InsertOrUpdateEmployee
       @ID INT,
       @Name VARCHAR(50),
       @ItemName VARCHAR(50),  
       @ItemCatName VARCHAR(50),
       @ItemQty DECIMAL(15,2)
AS BEGIN
    IF NOT EXISTS (SELECT * FROM dbo.Table1 WHERE ID = @ID)
       INSERT INTO dbo.Table1(ID, Name, ItemName, ItemCatName, ItemQty)
       VALUES(@ID, @Name, @ItemName, @ItemCatName, @ItemQty)
    ELSE
       UPDATE dbo.Table1
       SET Name = @Name,
           ItemName = @ItemName,
           ItemCatName = @ItemCatName,
           ItemQty = @ItemQty
       WHERE ID = @ID
END

en dan gewoon die opgeslagen procedure aanroepen vanuit je ADO.NET-code

Antwoord 2, autoriteit 73%

U kunt @@ROWCOUNTgebruiken om te controleren of een rij moet worden ingevoegd of bijgewerkt:

update table1 
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'
if @@ROWCOUNT = 0
insert into table1(id, name, itemname, itemcatName, itemQty)
values('val1', 'val2', 'val3', 'val4', 'val5')

in dit geval als de update mislukt, wordt de nieuwe rij ingevoegd

Antwoord 3, autoriteit 20%

U kunt controleren of de rij bestaat en vervolgens INSERT of UPDATE, maar dit garandeert dat u twee SQL-bewerkingen uitvoert in plaats van één:

1. controleer of rij bestaat
2. rij invoegen of bijwerken

Een betere oplossing is om altijd eerst BIJWERKEN, en als er geen rijen zijn bijgewerkt, doe dan een INSERT, zoals:

update table1 
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'
if @@ROWCOUNT = 0
  insert into table1(id, name, itemname, itemcatName, itemQty)
  values('val1', 'val2', 'val3', 'val4', 'val5')

Hiervoor zijn ofwel één SQL-bewerkingen nodig, of twee SQL-bewerkingen, afhankelijk van of de rij al bestaat.

Maar als prestatie echt een probleem is, moet je uitzoeken of de bewerkingen eerder INSERT’s of UPDATE’s zijn. Als UPDATE’s vaker voorkomen, doe dan het bovenstaande. Als INSERT’s vaker voorkomen, kunt u dat in omgekeerde volgorde doen, maar moet u foutafhandeling toevoegen.

BEGIN TRY
  insert into table1(id, name, itemname, itemcatName, itemQty)
  values('val1', 'val2', 'val3', 'val4', 'val5')
END TRY
BEGIN CATCH
  update table1 
  set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
  where id = 'val1'
END CATCH

Om echt zeker te zijn of u een UPDATE of INSERT moet doen, moet u twee bewerkingen uitvoeren binnen één enkele TRANSACTIE. Theoretisch zou de database direct na de eerste UPDATE of INSERT (of zelfs de EXISTS-controle), maar vóór de volgende INSERT/UPDATE-instructie, kunnen zijn gewijzigd, waardoor de tweede instructie toch mislukt. Dit is buitengewoon zeldzaam en de overhead voor transacties is het misschien niet waard.

U kunt ook een enkele SQL-bewerking met de naam MERGE gebruiken om een INSERT of een UPDATE uit te voeren, maar dat is waarschijnlijk ook overdreven voor deze bewerking met één rij.

Overweeg om te lezen over SQL-transactieafschriften, racevoorwaarden, SQL MERGE-statement.

Antwoord 4, autoriteit 18%

Hier is een nuttig artikel van Michael J. Swart over deze kwestie, dat verschillende patronen en antipatronen behandelt voor het implementeren van UPSERTin SQL Server:
https://michaeljswart.com/2017/07/sql- server-upsert-patterns-and-antipatterns/

Het lost bijbehorende gelijktijdigheidsproblemen op (overtredingen van primaire sleutels, deadlocks) – alle antwoorden die hier worden gegeven, worden in het artikel als antipatronen beschouwd (behalve de @Bridge-oplossing die triggers gebruikt, die daar niet wordt behandeld).

Hier is een uittreksel uit het artikel met de door de auteur geprefereerde oplossing:

In een serialiseerbare transactie met vergrendelingstips:

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRAN
    IF EXISTS ( SELECT * FROM dbo.AccountDetails WITH (UPDLOCK) WHERE Email = @Email )
      UPDATE dbo.AccountDetails
         SET Etc = @Etc
       WHERE Email = @Email;
    ELSE 
      INSERT dbo.AccountDetails ( Email, Etc )
      VALUES ( @Email, @Etc );
  COMMIT

Er is ook een gerelateerde vraag met antwoorden hier op stackoverflow: Invoegen Update opgeslagen proces op SQL Server

Antwoord 5, autoriteit 4%

Je zou dit kunnen doen met een INSTEAD OF INSERTtrigger op de tabel, die controleert op het bestaan van de rij en vervolgens updates/inserts, afhankelijk van of deze al bestaat. Er is een voorbeeld van hoe dit te doen voor SQL Server 2000+ op MSDN hier:

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate
      FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
      FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END