Einfügen, Aktualisieren und Löschen von Daten mithilfe von MERGE
In SQL Server 2008 können Sie mithilfe der MERGE-Anweisung in einer einzelnen Anweisung INSERT-, UPDATE- und DELETE-Vorgänge ausführen. In der MERGE-Anweisung können Sie eine Datenquelle mit einer Zieltabelle oder einer Zielsicht verknüpfen und für das Ziel basierend auf den Ergebnissen dieser Verknüpfung dann mehrere Aktionen ausführen. Beispielsweise können Sie die MERGE-Anweisung verwenden, um die folgenden Aufgaben auszuführen:
Fügen Sie Zeilen in eine einzelne Zieltabelle bedingt ein, bzw. aktualisieren Sie die Zeilen in Abhängigkeit von Bedingungen.
Wenn die Zeile in der Zieltabelle vorhanden ist, aktualisieren Sie eine oder mehrere Spalten. Andernfalls fügen Sie die Daten in eine neue Zeile ein.
Synchronisieren Sie zwei Tabellen.
Fügen Sie Zeilen in eine Zieltabelle basierend auf den Unterschieden zu den Quelldaten ein, bzw. aktualisieren oder löschen Sie diese.
Die MERGE-Syntax besteht aus fünf primären Klauseln:
Die MERGE-Klausel gibt die Tabelle oder Sicht an, die das Ziel für die INSERT-, UPDATE- oder DELETE-Vorgänge darstellt.
Die USING-Klausel gibt die Datenquelle an, die mit dem Ziel verknüpft wird.
Die ON-Klausel gibt die Verknüpfungsbedingungen an, die bestimmen, ob Ziel und Quelle übereinstimmen.
Die WHEN-Klauseln (WHEN MATCHED, WHEN NOT MATCHED BY TARGET und WHEN NOT MATCHED BY SOURCE) geben die Aktionen an, die nach Maßgabe der Ergebnisse der ON-Klausel und etwaiger weiterer in den WHEN-Klauseln angegebener Suchkriterien ausgeführt werden sollen.
Die OUTPUT-Klausel gibt für jede Zeile im Ziel, die eingefügt, aktualisiert oder gelöscht wird, eine Zeile zurück.
Ausführliche Informationen zu Syntax und Regeln finden Sie unter MERGE (Transact-SQL).
Angeben von Quell- und Zielsuchbedingungen
Sie müssen sich mit der Zusammenführung der Quell- und Zieldaten zu einem einzelnen Eingabestream sowie der Verwendung zusätzlicher Suchkriterien zum ordnungsgemäßen Filtern nicht benötigter Zeilen vertraut machen. Andernfalls geben Sie die zusätzlichen Suchkriterien möglicherweise so an, dass falsche Ergebnisse entstehen.
Zeilen in der Quelle werden anhand des in der ON-Klausel angegebenen Verknüpfungsprädikats mit Zeilen im Ziel verglichen. Das Ergebnis ist ein kombinierter Eingabestream. Pro Eingabezeile wird ein Einfüge-, Aktualisierungs- oder Löschvorgang ausgeführt. Je nach WHEN-Klauseln in der Anweisung kann die Eingabezeile mit einer der folgenden überstimmen:
Ein zueinander passendes Paar aus einer Zeile aus dem Ziel und einer Zeile aus der Quelle. Dies ist das Ergebnis der WHEN MATCHED-Klausel.
Eine Zeile aus der Quelle, für die im Ziel keine entsprechende Zeile vorhanden ist. Dies ist das Ergebnis der WHEN NOT MATCHED BY TARGET-Klausel.
Eine Zeile aus dem Ziel, für die in der Quelle keine entsprechende Zeile vorhanden ist. Dies ist das Ergebnis der WHEN NOT MATCHED BY SOURCE-Klausel.
Die Kombination von in der MERGE-Anweisungen angegebenen WHEN-Klauseln bestimmt den vom Abfrageprozessor implementierten Verknüpfungstyp und wirkt sich auf den resultierenden Eingabestream aus. Ziehen Sie zur Veranschaulichung die folgenden Beispiele für Quell- und Zieltabellen und -daten in Betracht.
USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
In der folgenden Tabelle sind die möglichen Verknüpfungstypen aufgeführt. Zudem ist angegeben, in welchen Fällen die einzelnen Typen vom Abfrageoptimierer implementiert werden. In der Tabelle ist außerdem der resultierende Eingabestream für die Quell- und Zieltabellen des Beispiels angegeben, wenn als Suchkriterium für den Vergleich von Quell- und Zieldaten Source.EmployeeID = Target.EmployeeID verwendet wird.
Verknüpfungstyp |
Implementierung |
Beispielergebnisse für Eingabestream |
|---|---|---|
INNER JOIN |
Die WHEN MATCHED-Klausel ist als einzige WHEN-Klausel angegeben. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- ------- NULL NULL NULL NULL |
LEFT OUTER JOIN |
Die WHEN NOT MATCHED BY TARGET-Klausel ist angegeben, und die WHEN NOT MATCHED BY SOURCE-Klausel ist nicht angegeben. Die WHEN MATCHED-Klausel kann, muss aber nicht angegeben sein. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL |
RIGHT OUTER JOIN |
Die WHEN MATCHED-Klausel und die WHEN NOT MATCHED BY SOURCE-Klausel sind angegeben, und die WHEN NOT MATCHED BY TARGET-Klausel ist nicht angegeben. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------NULL NULL 103 Bob NULL NULL 104 Steve |
FULL OUTER JOIN |
Die WHEN NOT MATCHED BY TARGET-Klausel und die WHEN NOT MATCHED BY SOURCE-Klausel sind angegeben. Die WHEN MATCHED-Klausel kann, muss aber nicht angegeben sein. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL NULL NULL 103 Bob NULL NULL 104 Steve |
ANTI SEMI JOIN |
Die WHEN NOT MATCHED BY SOURCE-Klausel ist als einzige WHEN-Klausel angegeben. |
TrgEmpID TrgName -------- ------- 100 Mary 101 Sara 102 Stefano |
Die Beispielergebnisse für den Eingabestream verdeutlichen, dass die Eingabestreamergebnisse von der Kombination von WHEN-Klauseln abhängen Nehmen Sie nun an, dass Sie für die Zieltabelle anhand dieses Eingabestreams die folgenden Aktionen ausführen möchten:
Einfügen von Zeilen aus der Quelltabelle, wenn die Mitarbeiter-ID in der Zieltabelle nicht vorhanden ist und der Name des Mitarbeiters in der Quelle mit 'S' beginnt.
Löschen von Zeilen in der Zieltabelle, wenn der Name des Mitarbeiters im Ziel mit 'S' beginnt und die Mitarbeiter-ID in der Quelltabelle nicht vorhanden ist.
Für diese Aktionen sind die folgenden WHEN-Klauseln erforderlich:
WHEN NOT MATCHED BY TARGET THEN INSERT
WHEN NOT MATCHED BY SOURCE THEN DELETE
Wie in der obigen Tabelle beschrieben, bildet der resultierende Eingabestream eine vollständige äußere Verknüpfung der Quell- und Zieltabelle, wenn beide WHEN NOT MATCHED-Klauseln angegeben sind. Da die Ergebnisse des Eingabestreams nun bekannt sind, können Sie Möglichkeiten zum Anwenden von Einfüge-, Aktualisierungs- und Löschaktionen auf den Eingabestream erwägen.
Wie bereits dargelegt, geben die WHEN-Klauseln die Aktionen an, die nach Maßgabe der Ergebnisse der ON-Klausel und etwaiger weiterer in den WHEN-Klauseln angegebener Suchkriterien ausgeführt werden sollen. In vielen Fällen erzeugen die in der ON-Klausel angegebenen Suchbedingungen den erforderlichen Eingabestream. Im Beispielszenario ist für die Einfüge- und Löschaktionen eine zusätzliche Filterung erforderlich, um die betreffenden Zeilen auf solche einzuschränken, die einen Mitarbeiternamen enthalten, der mit 'S' beginnt. Im folgenden Beispiel werden die Filterbedingungen auf die WHEN NOT MATCHED BY TARGET-Klausel und die WHEN NOT MATCHED BY SOURCE-Klausel angewendet. Die Ausgabe der Anweisung zeigt, dass die erwarteten Zeilen im Eingabestream korrigiert, eingefügt oder gelöscht werden.
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
Dies sind die Ergebnisse der OUTPUT-Klausel.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 104 Steve NULL NULL
(3 Zeile(n) betroffen)
Wenn Sie die Anzahl der Zeilen im Eingabestream frühzeitig verringern, indem Sie die zusätzliche Suchbedingung in der ON-Klausel angeben (beispielsweise durch Angabe von ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%'), wird die Abfrageleistung möglicherweise verbessert. Dies kann zu unerwarteten und falschen Ergebnissen führen. Da die in der ON-Klausel angegebenen zusätzlichen Suchbedingungen nicht für den Vergleich der Quell- und Zieldaten verwendet werden, können diese falsch angewendet werden.
Im folgenden Beispiel wird veranschaulicht, wie falsche Ergebnisse entstehen können. Die Suchbedingung für den Vergleich der Quell- und Zieltabellen sowie die zusätzliche Suchbedingung zum Filtern der Zeilen werden jeweils in der ON-Klausel angegeben. Da die zusätzliche Suchbedingung nicht erforderlich ist, um die Übereinstimmung von Quelle und Ziel zu bestimmen, werden die Einfüge- und Löschaktionen auf alle Eingabezeilen angewendet. Daher wird die Filterbedingung, EmployeeName LIKE 'S%' ignoriert. Wenn die Anweisung ausgeführt wird, zeigt die Ausgabe der inserted-Tabelle und der deleted-Tabelle, dass zwei Zeilen falsch geändert wurden: Mary wurde fälschlicherweise in der Zieltabelle gelöscht, und Bob wurde fälschlicherweise eingefügt.
-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%'
AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO
Dies sind die Ergebnisse der OUTPUT-Klausel.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 Mary
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 103 Bob NULL NULL
INSERT 104 Steve NULL NULL
(5 Zeile(n) betroffen)
Richtlinien für Suchbedingungen
Um richtige Ergebnisse zu erhalten, müssen die für den Vergleich von Quell- und Zielzeilen verwendeten Suchbedingungen sowie die zusätzlichen Suchbedingungen zum Filtern von Zeilen in der Quelle oder im Ziel richtig angegeben werden. Die folgenden Richtlinien sollten dabei beachtet werden:
Geben Sie in der ON <merge_search_condition>-Klausel nur Suchbedingungen an, die die Kriterien für den Vergleich von Daten in der Quell- und der Zieltabelle bestimmen. Geben Sie also nur Spalten aus der Zieltabelle an, die mit den entsprechenden Spalten der Quelltabelle verglichen werden.
Fügen Sie keine Vergleiche mit anderen Werten, z. B. einer Konstante, ein.
Zum Filtern von Zeilen aus der Quell- oder Zieltabelle verwenden Sie eine der folgenden Methoden:
Geben Sie die Suchbedingung für die Zeilenfilterung in der entsprechenden WHEN-Klausel an. Beispiel: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Definieren Sie in der Quelle oder im Ziel eine Sicht, die die gefilterten Zeilen zurückgibt, und verweisen Sie auf die Sicht als Quell- oder Zieltabelle. Wenn die Sicht für die Zieltabelle definiert ist, müssen alle Aktionen für die Tabelle die Bedingungen zum Aktualisieren von Sichten erfüllen. Weitere Informationen zum Aktualisieren von Daten mithilfe von Sichten finden Sie unter Ändern von Daten über eine Sicht.
Mit der WITH <common table expression>-Klausel können Sie Zeilen aus der Quell- oder Zieltabelle filtern. Diese Methode ähnelt dem Angeben zusätzlicher Suchkriterien in der ON-Klausel und kann zu falschen Ergebnissen führen. Es wird empfohlen, die Verwendung dieser Methode zu vermeiden oder diese vor dem Implementieren gründlich zu testen.
Beispiele
A. Verwenden einer einfachen MERGE-Anweisung zum Ausführen von INSERT- und UPDATE-Vorgängen
Angenommen, in der Tabelle FactBuyingHabits in einer Data Warehouse-Datenbank wird gespeichert, an welchem Datum die einzelnen Benutzer ein bestimmtes Produkt zuletzt gekauft haben. In einer zweiten Tabelle, Purchases, in einer OLTP-Datenbank werden die Einkäufe während einer bestimmten Woche gespeichert. Sie möchten wöchentlich Zeilen für Produkte, die bestimmte Kunden noch nie zuvor gekauft haben, aus der Tabelle Purchases der Tabelle FactBuyingHabits hinzufügen. Bei Zeilen von Kunden, die schon zuvor gekaufte Produkte erwerben, soll nur das Kaufdatum in der Tabelle FactBuyingHabits aktualisiert werden. Mithilfe der MERGE-Anweisung können diese INSERT- und UPDATE-Vorgänge in einer einzelnen Anweisung ausgeführt werden.
Im folgenden Beispiel werden zuerst die Tabellen Purchases und FactBuyingHabits erstellt und mit Beispieldaten gefüllt. Die Leistung bei MERGE-Anweisungen wird verbessert, wenn UNIQUE-Indizes für den Verknüpfungsschlüssel erstellt werden. Auf diese Weise werden gruppierte Indizes erstellt, indem für die ProductID-Spalte in beiden Tabellen eine PRIMARY KEY-Einschränkung festgelegt wird.
In diesem Beispiel enthält Purchases Einkäufe für die Woche ab dem 21. August 2006. FactBuyingHabits enthält Einkäufe für die vorherige Woche. Normalerweise würde diese Tabelle mit erheblich älteren Zeilen gefüllt werden.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL
DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
ProductID int, CustomerID int, PurchaseDate datetime,
CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
ProductID int, CustomerID int, LastPurchaseDate datetime,
CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO
Die Tabellen werden jetzt mit den folgenden Daten gefüllt:
dbo.Purchases
ProductID CustomerID PurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-20 00:00:00.000
707 15160 2006-08-25 00:00:00.000
708 18529 2006-08-21 00:00:00.000
711 11794 2006-08-20 00:00:00.000
711 19585 2006-08-22 00:00:00.000
712 14680 2006-08-26 00:00:00.000
712 21524 2006-08-26 00:00:00.000
712 19072 2006-08-20 00:00:00.000
870 15160 2006-08-23 00:00:00.000
870 11927 2006-08-24 00:00:00.000
870 18749 2006-08-25 00:00:00.000
dbo.FactBuyingHabits
ProductID CustomerID LastPurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-14 00:00:00.000
707 18178 2006-08-18 00:00:00.000
864 14114 2006-08-18 00:00:00.000
866 13350 2006-08-18 00:00:00.000
866 20201 2006-08-15 00:00:00.000
867 20201 2006-08-14 00:00:00.000
869 19893 2006-08-15 00:00:00.000
870 17151 2006-08-18 00:00:00.000
870 15160 2006-08-17 00:00:00.000
871 21717 2006-08-17 00:00:00.000
871 21163 2006-08-15 00:00:00.000
871 13350 2006-08-15 00:00:00.000
873 23381 2006-08-15 00:00:00.000
Es gibt zwei Zeilen mit Produkt und Kunde, die in beiden Tabellen enthalten sind: Kunde 11794 hat Produkt 707 in der aktuellen Woche sowie in der Woche zuvor gekauft, ebenso wie Kunde 15160, der Produkt 870 gekauft hat. Bei diesen Zeilen wird FactBuyingHabits mit dem in Purchases erfassten Datum für diese Einkäufe aktualisiert. Hierfür wird die WHEN MATCHED THEN-Klausel verwendet. Alle anderen Zeilen werden mit der WHEN NOT MATCHED THEN-Klausel in FactBuyingHabits eingefügt.
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;
B. Ausführen von UPDATE- und DELETE-Vorgängen
Im folgenden Beispiel wird die Tabelle ProductInventory in der AdventureWorks-Beispieldatenbank täglich mit MERGE aktualisiert. Dies erfolgt auf der Grundlage der in der Tabelle SalesOrderDetail verarbeiteten Bestellungen. Mithilfe der folgenden MERGE-Anweisung wird die Spalte Quantity in der Tabelle ProductInventory aktualisiert, indem die Anzahl der täglich für die einzelnen Produkte eingegangenen Bestellungen subtrahiert wird. Wenn die Anzahl der Bestellungen für ein Produkt dazu führt, dass der Produktbestand auf 0 oder niedriger fällt, wird die Zeile für dieses Produkt aus der Tabelle ProductInventory gelöscht. Beachten Sie, dass die Quelltabelle in der Spalte ProductID aggregiert wird. Ohne diesen Schritt könnte mehr als eine ProductID in der Quelltabelle mit der Zieltabelle übereinstimmen und dazu führen, dass die MERGE-Anweisung einen Fehler zurückgibt.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
GO
EXECUTE Production.usp_UpdateInventory '20030501'
C. Ausführen von INSERT-, UPDATE- und DELETE-Vorgängen
Im folgenden Beispiel wird die MERGE-Anweisung verwendet, um Zeilen in eine Zieltabelle basierend auf den Unterschieden zu den Quelldaten einzufügen bzw. diese zu aktualisieren oder zu löschen. Betrachten Sie ein kleines Unternehmen mit fünf Abteilungen, wobei jede Abteilung von einem Abteilungsmanager geleitet wird. Das Unternehmen möchte die Abteilungen neu organisieren, Die MERGE-Anweisung muss die folgenden Änderungen implementieren, um die Ergebnisse der Neuorganisation in die Zieltabelle dbo.Departments einzufügen:
In einigen vorhandenen Abteilungen ändert sich nichts.
Einige vorhandene Abteilungen erhalten neue Manager.
Einige Abteilungen werden neu gegründet.
Einige Abteilungen wird es nach der Neuorganisation nicht mehr geben.
Der folgende Code erstellt die Zieltabelle dbo.Departments und füllt diese mit Managern.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL
DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments
VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
(3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
(5, 'Manufacturing', 'Brewer');
Die Organisationsänderungen, die für die Abteilungen vorgenommen werden sollen, werden in der Quelltabelle dbo.Departments_delta gespeichert. Der folgende Code erstellt und füllt diese Tabelle:
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL
DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES
(1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
(3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),
(6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO
Damit die Neuorganisation des Unternehmens in der Zieltabelle dargestellt wird, verwendet der folgende Code die MERGE-Anweisung zum Vergleichen der Quelltabelle dbo.Departments_delta mit der Zieltabelle dbo.Departments. Die Suchbedingung für diesen Vergleich wird in der ON-Klausel der Anweisung definiert. Basierend auf den Ergebnissen des Vergleichs werden die folgenden Aktionen ausgeführt.
Abteilungen, die in beiden Tabellen vorhanden sind, werden in der Zieltabelle mit neuen Namen, neuen Managern oder beidem in der Tabelle Departments aktualisiert. Wenn es keine Änderungen gibt, erfolgt keine Aktualisierung. Dies wird in der WHEN MATCHED THEN-Klausel umgesetzt.
Alle Abteilungen in Departments_delta, die in Departments nicht vorhanden sind, werden in Departments eingefügt. Dies wird in der WHEN NOT MATCHED THEN-Klausel umgesetzt.
Alle Abteilungen in Departments, die nicht in der Quelltabelle Departments_delta vorhanden sind, werden in Departments gelöscht. Dies wird mithilfe der WHEN NOT MATCHED BY SOURCE THEN-Klausel erreicht.
MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,
inserted.Manager AS SourceManager,
deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,
deleted.Manager AS TargetManager;
Siehe auch