Einfügen, Aktualisieren und Löschen von Daten mithilfe von MERGE

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.

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.*; 

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'

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;