Inleiding tot de MERGE-instructie en SQL Server-gegevenswijziging
De MERGE-instructie wordt gebruikt om wijzigingen aan te brengen in één tabel op basis van waarden die overeenkomen met de andere. Het kan worden gebruikt om bewerkingen voor invoegen, bijwerken en verwijderen in één instructie te combineren. In dit artikel zullen we onderzoeken hoe u de MERGE-instructie kunt gebruiken. We bespreken enkele best practices, beperkingen en sluiten af met verschillende voorbeelden.
Dit is het vijfde artikel in een reeks artikelen. U kunt bij het begin beginnen door Inleiding tot SQL Server Data Modification Statements te lezen.
Alle voorbeelden voor deze les zijn gebaseerd op Microsoft SQL Server Management Studio en de AdventureWorks2012-database. U kunt aan de slag gaan met het gebruik van deze gratis tools met behulp van mijn gids Aan de slag met SQL Server
Voordat we beginnen
Hoewel dit artikel de AdventureWorks-database gebruikt voor zijn voorbeelden, heb ik besloten om verschillende voorbeeldtabellen voor gebruik in de database om de behandelde concepten beter te illustreren. U vindt het script dat u nodig heeft om hier uit te voeren. Merk op dat er een speciale sectie is die betrekking heeft op MERGE.
Basisstructuur
De MERGE-instructie combineert INSERT-, DELETE- en UPDATE-bewerkingen in één tabel. Als u eenmaal begrijpt hoe het werkt, zult u zien dat het de procedure vereenvoudigt door alle drie de instructies afzonderlijk te gebruiken om gegevens te synchroniseren.
Hieronder vindt u een algemene indeling voor de samenvoeginstructie.
MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement
De merge-instructie werkt met twee tabellen, de sourceTable en targetTable. De targetTable is de tabel die moet worden gewijzigd op basis van de gegevens in de sourceTable.
De twee tabellen worden vergeleken met behulp van een mergeCondition . Deze voorwaarde geeft aan hoe rijen uit de sourceTable worden vergeleken met de targetTable. Als u bekend bent met INNER JOINS, kunt u dit beschouwen als de join-voorwaarde die wordt gebruikt om rijen te matchen.
Meestal komt u overeen met een unieke ID, zoals een primaire sleutel. Als de brontabel NewProduct en target ProductMaster was en de primaire sleutel voor beide ProductID, dan zou een goede samenvoegvoorwaarde om te gebruiken zijn:
NewProduct.ProductID = ProductMaster.ProductID
Er resulteert een samenvoegvoorwaarde in een van de drie staten: MATCHED, NOT MATCHED of NOT MATCHED BY SOURCE.
Voorwaarden samenvoegen
Laten we eens kijken wat de verschillende voorwaarden betekenen:
MATCHED – dit zijn rijen die voldoen aan de matchconditie. Ze zijn gemeenschappelijk voor zowel de bron- als de doeltabellen. In ons diagram worden ze groen weergegeven. Wanneer u deze voorwaarde gebruikt in een fusieverklaring u; het meest als het bijwerken van de doelrij-kolommen met sourceTable-kolomwaarden.
NOT MATCHED – Dit staat ook bekend als NOT MATCHED BY TARGET; dit zijn rijen uit de brontabel die niet overeenkomen met rijen in de doeltabel. Deze rijen worden weergegeven door het blauwe gebied erboven. In de meeste gevallen kan dit worden gebruikt om te concluderen dat de bronrijen moeten worden toegevoegd aan de doeltabel.
NIET AANGEPAST DOOR BRON – dit zijn rijen in de doeltabel die nooit werden gevonden door een bronrecord; dit zijn de rijen in het oranje gebied. Als het uw doel is om de targetTable-gegevens volledig te synchroniseren met de bron, dan gebruikt u deze overeenkomstvoorwaarde om rijen te VERWIJDEREN.
Als u niet begrijpt hoe dit werkt, overweeg dan dat de samenvoegvoorwaarde is als een join-voorwaarde. RIJEN in het groene gedeelte vertegenwoordigen rijen die overeenkomen met de samenvoegvoorwaarde, rijen in het blauwe gedeelte zijn de rijen die in de Brontabel staan, maar niet in het doel. De rijen in het oranje gedeelte zijn de rijen die alleen in het doel worden gevonden.
Geef deze overeenkomende scenario’s, u kunt eenvoudig activiteiten toevoegen, verwijderen en bijwerken in een enkele instructie opnemen om wijzigingen tussen twee tabellen.
Laten we naar een voorbeeld kijken.
Voorbeeld SAMENVOEGEN
Laten we aannemen dat het ons doel is om alle wijzigingen die in esqlProductSource zijn aangebracht te synchroniseren met esqlProductTarget. Hier is een diagram van deze twee tabellen:
Opmerking: omwille van dit voorbeeld heb ik de scripts uitgevoerd waarover ik sprak in de inleiding om twee tabellen te maken en te vullen: esqlProductSource en esqlProductTarget.
Laten we, voordat we de instructie MERGE maken, bekijken hoe we de tabel zouden synchroniseren met behulp van de instructie UPDATE, INSERT en DELETE om deze te wijzigen, toe te voegen , en verwijder rijen in de doeltabel.
Ik denk dat als je eenmaal ziet hoe we dit afzonderlijk doen, het logischer is om te zien hoe we dit doen in één bewerking.
UPDATE gebruiken om wijzigingen te synchroniseren van de ene tabel naar de volgende
Om de doeltabel bij te werken met de gewijzigde waarden in de productbron, kunnen we een UPDATE-instructie gebruiken. Aangezien de ProductID de primaire sleutel van beide tabellen is, wordt dit onze beste keuze om rijen tussen de tabellen te matchen.
Als we de kolomwaarden in de doeltabel zouden bijwerken met behulp van de bronkolommen, zouden we dat kunnen doen met de volgende update-instructie
UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID
Deze instructie zal de kolom in esqlProductTarget bijwerken met overeenkomstige kolomwaarden die in esqlProductSource zijn gevonden voor overeenkomende productID’s.
INSERT Rijen gevonden in de ene tabel maar niet in de andere
Laten we nu eens kijken hoe we kan de rijen uit de brontabel identificeren die we in het productdoel moeten invoegen. Om dit te doen, kunnen we subquery gebruiken om rijen in de brontabel te vinden die niet in het doel staan.
INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)
Opmerking: ik zou ook een outer join kunnen gebruiken hetzelfde doen. Als je geïnteresseerd bent in waarom, bekijk dan dit artikel.
Met deze instructie wordt een nieuwe rij in esqlProductTarget ingevoegd vanuit alle rijen in esqlProductSource die niet in esqlProductTarget worden gevonden.
Verwijderen Rijen
Die laatste synchronisatieactiviteit die we moeten doen, het verwijdert alle rijen in de doeltabel die niet in SQL Source staan. Net als bij de instructie insert, gebruiken we een subquery. Maar deze keer vinden we rijen in esqlProductTarget die niet in esqlProductSource zijn gevonden. Hier is de DELETE-instructie die we kunnen gebruiken:
DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)
Nu je hebt gezien hoe je de verschillende bewerkingen afzonderlijk kunt uitvoeren, laten we zien hoe ze samenkomen in de merge statement.
Merk op dat daar het grootste deel van het zware werk wordt gedaan door de samenvoegconditie en de resultaten ervan. In plaats van de match herhaaldelijk op te zetten, zoals bij de delete-instructie, wordt het één keer gedaan.
Vergelijk opnieuw de Insert-instructie met de merge-instructie hierboven.
INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)
Gezien de MERGE-instructie de bron- en doeltabel bepaalt, evenals hoe ze overeenkomen, is alles met een kleurcode in het rood overbodig; daarom niet in het invoeggedeelte van de samenvoeging.
MERGE-wijzigingen loggen met OUTPUT
Je kunt de OUTPUT-clausule gebruiken om eventuele wijzigingen te loggen. In dit geval kan de speciale variabele $ action worden gebruikt om de samenvoegactie te loggen. Deze variabele zal een van de volgende drie waarden aannemen: “INSERT”, “UPDATE” of “DELETE”.
We zullen ons voorbeeld blijven gebruiken, maar deze keer zullen we de wijzigingen loggen en de wijzigingen.
Als het bovenstaande wordt uitgevoerd op nieuwe voorbeeldgegevens, wordt de volgende samenvatting gegenereerd:
