Einführung in die MERGE-Anweisung und die SQL Server-Datenänderung
Mit der MERGE-Anweisung werden Änderungen in einer Tabelle basierend auf Werten vorgenommen, die mit anderen übereinstimmen. Es kann verwendet werden, um Einfüge-, Aktualisierungs- und Löschvorgänge in einer Anweisung zu kombinieren. In diesem Artikel erfahren Sie, wie Sie die MERGE-Anweisung verwenden. Wir diskutieren einige bewährte Methoden, Einschränkungen und Zusammenfassungen anhand mehrerer Beispiele.
Dies ist der fünfte Artikel in einer Reihe von Artikeln. Sie können am Anfang mit der Einführung in SQL Server-Datenänderungsanweisungen beginnen.
Alle Beispiele für diese Lektion basieren auf Microsoft SQL Server Management Studio und der AdventureWorks2012-Datenbank. Sie können mit diesen kostenlosen Tools in meinem Handbuch Erste Schritte mit SQL Server
beginnen, bevor wir beginnen
Obwohl dieser Artikel die AdventureWorks-Datenbank für seine Beispiele verwendet, habe ich mich für die Erstellung entschieden Mehrere Beispieltabellen zur Verwendung in der Datenbank zur besseren Veranschaulichung der behandelten Konzepte. Das Skript, das Sie ausführen müssen, finden Sie hier. Beachten Sie, dass es einen speziellen Abschnitt zu MERGE gibt.
Grundstruktur
Die MERGE-Anweisung kombiniert INSERT-, DELETE- und UPDATE-Operationen in einer Tabelle. Sobald Sie verstanden haben, wie es funktioniert, werden Sie feststellen, dass es die Vorgehensweise vereinfacht, indem alle drei Anweisungen separat zum Synchronisieren von Daten verwendet werden.
Nachfolgend finden Sie ein verallgemeinertes Format für die Zusammenführungsanweisung.
MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement
Die Merge-Anweisung verwendet zwei Tabellen, die sourceTable und die targetTable. Die Zieltabelle ist die Tabelle, die basierend auf den in der Quelltabelle enthaltenen Daten geändert werden soll.
Die beiden Tabellen werden unter Verwendung einer mergeCondition verglichen . Diese Bedingung gibt an, wie Zeilen aus der Quelltabelle mit der Zieltabelle abgeglichen werden. Wenn Sie mit INNER JOINS vertraut sind, können Sie sich dies als Verknüpfungsbedingung vorstellen, die zum Abgleichen von Zeilen verwendet wird.
In der Regel stimmen Sie mit einer eindeutigen Kennung überein, z. B. einem Primärschlüssel. Wenn die Quelltabelle NewProduct und Target ProductMaster und der Primärschlüssel für beide ProductID wäre, wäre eine gute Zusammenführungsbedingung:
NewProduct.ProductID = ProductMaster.ProductID
Eine Zusammenführungsbedingung ergibt sich in einem von drei Zuständen: MATCHED, NOT MATCHED oder NOT MATCHED BY SOURCE.
Zusammenführungsbedingungen
Lassen Sie uns untersuchen, was die verschiedenen Bedingungen bedeuten:
MATCHED – Dies sind Zeilen, die die Übereinstimmungsbedingung erfüllen. Sie sind sowohl der Quell- als auch der Zieltabelle gemeinsam. In unserem Diagramm werden sie grün angezeigt. Wenn Sie diese Bedingung in einer Fusionserklärung verwenden, werden Sie; Am ehesten werden die Zielzeilenspalten mit den Spaltenwerten von sourceTable aktualisiert.
NOT MATCHED – Dies wird auch als NOT MATCHED BY TARGET bezeichnet. Dies sind Zeilen aus der Quelltabelle, die mit keinen Zeilen in der Zieltabelle übereinstimmen. Diese Zeilen werden durch den blauen Bereich oben dargestellt. In den meisten Fällen kann daraus geschlossen werden, dass die Quellzeilen zur Zieltabelle hinzugefügt werden sollen.
NICHT MIT DER QUELLE ZUSAMMENGEPASST – Dies sind Zeilen in der Zieltabelle, die von einem Quelldatensatz nie abgeglichen wurden. Dies sind die Zeilen im orangefarbenen Bereich. Wenn Sie die targetTable-Daten vollständig mit der Quelle synchronisieren möchten, verwenden Sie diese Übereinstimmungsbedingung, um Zeilen zu LÖSCHEN.
Wenn Sie Probleme haben, die Funktionsweise zu verstehen, berücksichtigen Sie die Zusammenführungsbedingung eine Join-Bedingung. Zeilen im grünen Bereich stellen Zeilen dar, die der Zusammenführungsbedingung entsprechen. Zeilen im blauen Bereich sind die Zeilen, die in der SourceTable, jedoch nicht im Ziel gefunden wurden. Die Zeilen im orangefarbenen Abschnitt sind die Zeilen, die nur im Ziel gefunden werden.
Wenn Sie diese übereinstimmenden Szenarien angeben, können Sie Aktivitäten zum Hinzufügen, Entfernen und Aktualisieren einfach in eine einzelne Anweisung integrieren, um Änderungen zwischen zwei zu synchronisieren Tabellen.
Schauen wir uns ein Beispiel an.
MERGE-Beispiel
Nehmen wir an, unser Ziel ist es, alle an esqlProductSource vorgenommenen Änderungen mit esqlProductTarget zu synchronisieren. Hier ist ein Diagramm dieser beiden Tabellen:
Hinweis: In diesem Beispiel habe ich die Skripte ausgeführt, über die ich gesprochen habe In der Einführung zum Erstellen und Auffüllen von zwei Tabellen: esqlProductSource und esqlProductTarget.
Bevor wir die MERGE-Anweisung erstellen, schauen wir uns an, wie wir die Tabelle mit den Anweisungen UPDATE, INSERT und DELETE synchronisieren, um sie zu ändern und hinzuzufügen und entfernen Sie Zeilen in der Zieltabelle.
Wenn Sie einmal gesehen haben, wie wir dies einzeln tun, ist es sinnvoller, die Kombination in einer einzigen Operation zu sehen.
Verwenden von UPDATE zum Synchronisieren von Änderungen Von einer Tabelle zur nächsten
Um die Zieltabelle mit den geänderten Werten in der Produktquelle zu aktualisieren, können Sie eine UPDATE-Anweisung verwenden. Da die ProductID der Primärschlüssel beider Tabellen ist, wird sie zu unserer besten Wahl für Übereinstimmungszeilen zwischen den Tabellen.
Wenn wir die Spaltenwerte in der Zieltabelle mithilfe der Quellspalten aktualisieren möchten, können wir dies mit der folgenden Aktualisierungsanweisung tun.
UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID
Diese Anweisung aktualisiert die Spalte in esqlProductTarget mit den entsprechenden Spaltenwerten in esqlProductSource, um die Produkt-IDs abzugleichen.
INSERT-Zeilen in einer Tabelle, aber nicht in der anderen
Schauen wir uns nun an, wie wir vorgehen kann die Zeilen aus der Quelltabelle identifizieren, die wir in das Produktziel einfügen müssen. Zu diesem Zweck können wir Unterabfragen verwenden, um Zeilen in der Quelltabelle zu finden, die nicht im Ziel enthalten sind.
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)
Hinweis: Ich könnte auch einen äußeren Join verwenden das Gleiche tun. Wenn Sie daran interessiert sind, warum, lesen Sie diesen Artikel.
Diese Anweisung fügt eine neue Zeile aus allen Zeilen in esqlProductSource, die nicht in esqlProductTarget enthalten sind, in esqlProductTarget ein.
Entfernen Zeilen
Bei dieser letzten Synchronisierungsaktivität werden alle Zeilen in der Zieltabelle entfernt, die sich nicht in SQL Source befinden. Wie bei der Einfügeanweisung verwenden wir eine Unterabfrage. Diesmal werden wir jedoch Identitätszeilen in esqlProductTarget identifizieren, die in esqlProductSource nicht gefunden wurden. Hier ist die DELETE-Anweisung, die wir verwenden können:
DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)
Nachdem Sie nun gesehen haben, wie die verschiedenen Operationen einzeln ausgeführt werden, können Sie sehen, wie sie in der Zusammenführungsanweisung.
Beachten Sie, dass der größte Teil des schweren Hebens durch die Zusammenführungsbedingung und ihre Ergebnisse erfolgt. Anstatt die Übereinstimmung wiederholt einrichten zu müssen, wie wir es in der delete-Anweisung getan haben, wird sie einmal ausgeführt.
Vergleichen Sie die Insert-Anweisung erneut mit der obigen merge-Anweisung.
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)
Wenn die MERGE-Anweisung die Quell- und Zieltabelle sowie deren Übereinstimmung festlegt, ist alles, was in Rot farbcodiert ist, redundant. Daher nicht im Einfügeteil der Zusammenführung.
Protokollieren von MERGE-Änderungen mit OUTPUT
Mit der OUTPUT-Klausel können Sie alle Änderungen protokollieren. In diesem Fall kann die spezielle Variable $ action verwendet werden, um die Zusammenführungsaktion zu protokollieren. Diese Variable nimmt einen von drei Werten an: „INSERT“, „UPDATE“ oder „DELETE“.
Wir werden unser Beispiel weiterhin verwenden, aber dieses Mal werden wir die Änderungen protokollieren und die zusammenfassen Änderungen.
Wenn das oben Gesagte mit neuen Beispieldaten ausgeführt wird, wird die folgende Zusammenfassung generiert:
