Einfügen, Aktualisieren und Löschen von Daten mithilfe von MERGE
In SQL Server 2008 können Sie mithilfe der MERGE-Anweisung INSERT-, UPDATE- und DELETE-Vorgänge in einer einzelnen Anweisung ausführen. Mittels der MERGE-Anweisung können Sie eine Datenquelle mit einer Zieltabelle oder einer Zielsicht verknüpfen und, basierend auf den Ergebnissen dieses Joins, mehrere Aktionen für das Ziel ausführen. Beispielsweise können Sie die MERGE-Anweisung verwenden, um die folgenden Aufgaben auszuführen:
Fügen Sie in Abhängigkeit von Bedingungen Zeilen in eine Zieltabelle ein bzw. aktualisieren Sie Zeilen.
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 Joinbedingungen an, die feststellen, wenn 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 auf der Grundlage der Ergebnisse der ON-Klausel und möglicher weiterer, in den WHEN-Klauseln angegebenen 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 zur Syntax und zu den Regeln finden Sie unter MERGE (Transact-SQL).
Angeben von Quell- und Zielsuchbedingungen
Es ist wichtig, dass Sie verstehen, wie die Quell- und Zieldaten zu einem einzigen Eingabedatenstrom zusammengefasst werden und wie mittels zusätzlicher Suchkriterien nicht benötigte Zeilen herausgefiltert werden können. Andernfalls geben Sie die zusätzlichen Suchkriterien möglicherweise auf eine Art an, die zu falschen Ergebnissen führt.
Zeilen in der Quelle werden auf der Grundlage des in der ON-Klausel angegebenen Verknüpfungsprädikats mit Zeilen im Ziel verglichen. Das Ergebnis ist ein kombinierter Eingabedatenstrom. Pro Eingabezeile wird ein Einfüge-, Aktualisierungs- oder Löschvorgang ausgeführt. Abhängig von den WHEN-Klauseln in der Anweisung entspricht die Eingabezeile einer der folgenden Beschreibungen:
Ein zueinander passendes Paar aus einer Zeile im Ziel und einer Zeile in 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-Anweisung angegebenen WHEN-Klauseln bestimmt den vom Abfrageprozessor implementierten Verknüpfungstyp und wirkt sich auf den resultierenden Eingabedatenstrom aus. Die folgenden Beispiele für Quell- und Zieltabellen und -daten dienen der Veranschaulichung.
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 werden die möglichen Verknüpfungstypen aufgeführt. Zudem ist angegeben, in welchen Fällen jeweils die einzelnen Typen durch den Abfrageoptimierer implementiert werden. In der Tabelle wird außerdem der resultierende Eingabedatenstrom 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.
Jointyp |
Implementierung |
Beispielergebnisse für den Eingabedatenstrom |
|---|---|---|
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 Eingabedatenstrom zeigen, dass die Eingabedatenstrom-Ergebnisse von der Kombination von WHEN-Klauseln abhängig sind. Nehmen Sie nun an, dass Sie für die Zieltabelle auf der Grundlage dieses Eingabedatenstroms 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 Tabelle oben beschrieben, bildet der resultierende Eingabedatenstrom eine vollständige äußere Verknüpfung der Quell- und Zieltabelle, wenn beide WHEN NOT MATCHED-Klauseln angegeben sind. Da die Ergebnisse des Eingabedatenstroms nun bekannt sind, überlegen Sie, wie die Einfüge-, Aktualisierungs- und Löschaktionen auf den Eingabedatenstrom angewendet werden.
Wie bereits dargelegt, geben die WHEN-Klauseln die Aktionen an, die auf der Grundlage der Ergebnisse der ON-Klausel und etwaiger weiterer, in den WHEN-Klauseln angegebenen Suchkriterien ausgeführt werden sollen. In vielen Fällen erzeugen die in der ON-Klausel angegebenen Suchbedingungen den erforderlichen Eingabedatenstrom. Im Beispielszenario ist für die Einfüge- und Löschaktionen jedoch eine zusätzliche Filterung erforderlich, um die betreffenden Zeilen auf diejenigen 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 Eingabedatenstrom 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 Eingabedatenstrom 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 anscheinend verbessert. Dies kann jedoch 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 sie falsch angewendet werden.
Im folgenden Beispiel wird gezeigt, 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 Tabellen inserted und der deleted, dass zwei Zeilen falsch geändert wurden: Mary wurde in der Zieltabelle fälschlicherweise 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 korrekte 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. Es wird empfohlen, die folgenden Richtlinien zu beachten:
Geben Sie in der Klausel ON <merge_search_condition> nur Suchbedingungen an, die die Kriterien für den Vergleich von Daten in der Quell- und in 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 Herausfiltern 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 der Implementierung 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 Joinschlü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 AdventureWorks2008R2;
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 AdventureWorks2008R2-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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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