Databasetabellen updaten met MERGE

Hoe zorg je ervoor dat je website of webshop optimaal blijft presteren? In ieder geval dien je de hulpmiddelen die je hebt, op de juiste wijze te gebruiken. Juist gebruiken houdt ook in de juiste hulpmiddelen gebruiken voor een bepaald e taak. Vaak wordt de processorkracht van de applicatieserver gebruikt om databases bij te werken, terwijl dat veel efficiënter kan via de databaseserver zelf.

Hieronder leg ik uit hoe je gebruik kunt maken van de kracht van de databaseserver zelf om databasetabellen te updaten, met bijvoorbeeld data die je al eerder hebt geïmporteerd, zodat de applicatieserver –en dus je website of webshop- geen onnodige problemen ondervindt.

In SQL kan je rows updaten via UPDATE, aanmaken via INSERT en verwijderen met DELETE. In sommige gevallen, bijvoorbeeld na een BULK INSERT, wil je deze acties echter conditioneel kunnen uitvoeren: INSERT als een rij, bijvoorbeeld een product, nog niet bestaat, UPDATE als deze wel al bestaat, en DELETE wanneer de rij niet meer voorkomt in de nieuwe gegevens.

Dit kan van pas komen bij het importeren van producten in een webshop, bijvoorbeeld.

In dat geval kan je gebruik maken van MERGE. Met MERGE geef je een doeltabel op, waarvan de rijen aangepast moeten worden, en een brontabel, waarin de brondata staan. Op basis van aanwezigheid in doel- en/of brontabel, kan je bepalen welke actie moet worden uitgevoerd.

Werking MERGE

Een simpele MERGE-statement ziet er als volgt uit:

MERGE @DoelTabel as TargetUSING @BronTabel as Source      ON(Target.id = Source.id)WHEN MATCHED THEN      UPDATE SET Target.dateUpdate = GETDATE();

Eerst wordt aangegeven welke tabel gebruikt wordt als doeltabel (@DoelTabel in dit geval) en welke als brontabel. Beide tabellen krijgen een alias waarnaar verwezen wordt verderop in de code.

De derde regel geeft aan hoe we bepalen welke rijen in de brontabel overeenkomen met de rijen in de doeltabel. In dit geval beschouwt MERGE de rijen gelijk als de kolom id overeenkomt in beide tabellen.

Let op: dit houdt wel in dat de waarde in id (of welke unieke sleutel ook) maar één keer mag voorkomen in beide tabellen! Als er meer dan één unieke waarde is, dan zal SQL-server een foutmelding genereren.

Hierna wordt gecontroleerd of de derde regel (ON…) een overeenkomst geeft. Als dat het geval is, dus MATCHED, dan wordt de kolom dateUpdate bijgewerkt.

Er zijn twee andere mogelijkheden die uit de test kunnen komen: Een id (of welke andere waarde dan ook) zit wel in Source maar niet in Target, of id zit wel in Target maar niet in Source. In het eerste geval kan je dit opvangen via WHEN NOT MATCHED THEN en dan zal je waarschijnlijk een rij in de doeltabel willen aanmaken via INSERT. De tweede uitkomst vang je af met WHEN NOT MATCHED BY SOURCE en dat kan betekenen dat je de rij wilt verwijderen met DELETE.

Voorbeelden MERGE

Voorbeeldtabellen

/*

      Doeltabel.

 */

DECLARE @DoelTabel TABLE(

      id int,

      name nvarchar(100),

      dateInsert datetime,

      dateUpdate datetime

);


INSERT INTO @DoelTabel VALUES

      (1, 'Product 1', '11-19-2014', '11-19-2014'),

      (2, 'Product 2', '11-19-2014', '11-19-2014')

;

SELECT * FROM @DoelTabel;

/*

      Brontabel.

 */

DECLARE @BronTabel TABLE(

      id int,

      name nvarchar(100),

      dateInsert datetime

);


INSERT INTO @BronTabel VALUES

      (1, 'Product 1 - Aangepast', '11-19-2014'),

      (3, 'Product 3', '11-19-2014')

;


SELECT * FROM @BronTabel;

UPDATE

Gegeven bovenstaande doel- en brontabellen: Als de simpele MERGE-statement van eerder wordt uitgevoerd, dan zal dat ervoor zorgen dat de naam van Product 1 wordt aangepast en de dateUpdate wordt bijgewerkt:

MERGE @DoelTabel as Target

USING @BronTabel as Source

      ON(Target.id = Source.id)


WHEN MATCHED THEN

      UPDATE SET

            Target.name = Source.name,

            Target.dateUpdate = GETDATE()

;


SELECT * FROM @DoelTabel;

INSERT

Het product met id 3 staat nu nog steeds niet in de doeltabel, maar wel in de brontabel, dus om deze toe te voegen, gebruik je WHEN NOT MATCHED THEN:

MERGE @DoelTabel as Target

USING @BronTabel as Source

      ON(Target.id = Source.id)


WHEN MATCHED THEN

      UPDATE SET

            Target.name = Source.name,

            Target.dateUpdate = GETDATE()


WHEN 
NOT MATCHED THEN

 
INSERT (id, name, dateInsert, dateUpdate)

 
VALUES(

 
    Source.id,

 
    Source.name,

 
    GETDATE(), --Of Source.dateInsert

 
    GETDATE()

 
)

;


SELECT * FROM @DoelTabel;

DELETE

Product 2, dat niet voorkomt in de brontabel, blijft nog steeds staan in de doeltabel. Met WHEN NOT MATCHED BY SOURCE THEN wordt deze conditie opgevangen en met DELETE wordt het product verwijderd:

MERGE @DoelTabel as Target

USING @BronTabel as Source

      ON(Target.id = Source.id)


WHEN MATCHED THEN

      UPDATE SET

            Target.name = Source.name,

            Target.dateUpdate = GETDATE()

WHEN NOT MATCHED THEN

      INSERT (id, name, dateInsert, dateUpdate)

      VALUES(

            Source.id,

            Source.name,

            GETDATE(), --Of Source.dateInsert

            GETDATE()

      )


WHEN 
NOT MATCHED BY SOURCE THEN

 
DELETE

;


SELECT * FROM @DoelTabel;

Er zijn uiteraard meer manieren om ervoor te zorgen dat je snel je webshop kunt vullen met producten, dit is één van de methoden die wij toepassen voor onze klanten.

Wil je meer weten over het versnellen van je webshopbeheer? Bel of mail dan naar Sigma Solutions voor tips en adviezen.