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.