WITH <common_table_expression>

Gibt das temporäre benannte Resultset oder die temporäre benannte Sicht an, die auch als allgemeiner Tabellenausdruck bezeichnet und innerhalb der MERGE-Anweisung definiert werden. Das Resultset wird aus einer einfachen Abfrage abgeleitet. Die MERGE-Anweisung verweist auf dieses Resultset. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]

Gibt die Anzahl oder den Prozentsatz der betroffenen Zeilen an. expression kann entweder eine Anzahl oder einen Prozentsatz der Zeilen darstellen. Die Zeilen, auf die im TOP-Ausdruck verwiesen wird, sind nicht auf bestimmte Weise angeordnet. Weitere Informationen finden Sie unter TOP (Transact-SQL).

Die TOP-Klausel wird angewendet, nachdem die gesamte Quelltabelle und die gesamte Zieltabelle verknüpft und die nicht für eine der Aktionen INSERT, UPDATE oder DELETE in Frage kommenden verknüpften Zeilen gelöscht wurden. Die TOP-Klausel verringert zudem die Anzahl der verknüpften Zeilen auf den angegebenen Wert, und die INSERT-, UPDATE- oder DELETE-Aktionen werden ungeordnet auf die verbliebenen verknüpften Zeilen angewendet. Das heißt, für die Verteilung der Zeilen auf die in den WHEN-Klauseln definierten Aktionen gilt keine bestimmte Reihenfolge. Wenn beispielsweise TOP (10) angegeben wird, sind 10 Zeilen betroffen. Von diesen Zeilen können sieben aktualisiert und drei eingefügt werden, oder eine Zeile kann gelöscht, fünf können aktualisiert und vier eingefügt werden usw.

Da die MERGE-Anweisung einen vollständigen Tabellenscan der Quell- und der Zieltabelle ausführt, kann die E/A-Leistung beeinträchtigt werden, wenn mit der TOP-Klausel eine große Tabelle durch Erstellen mehrerer Batches geändert wird. In diesem Szenario muss unbedingt sichergestellt werden, dass alle aufeinander folgenden Batches auf neue Zeilen ausgerichtet sind. Weitere Informationen finden Sie unter Optimieren der Leistung bei MERGE-Anweisungen.

database_name

Der Name der Datenbank, in der sich target_table befindet.

schema_name

Der Name des Schemas, zu dem target_table gehört.

target_table

Die Tabelle oder Sicht, für die die Datenzeilen aus <table_source> anhand von <clause_search_condition> abgeglichen werden. target_table ist das Ziel der Einfüge-, Aktualisierungs- und Löschvorgänge, die durch die WHEN-Klauseln der MERGE-Anweisung angegeben werden.

Wenn target_table eine Sicht ist, müssen alle Aktionen für die Tabelle die Bedingungen zum Aktualisieren von Sichten erfüllen. Weitere Informationen finden Sie unter Ändern von Daten über eine Sicht.

target_table darf keine Remotetabelle sein, und für target_table dürfen keine Regeln definiert sein.

[ AS ] table_alias

Ist ein alternativer Name, über den auf eine Tabelle verwiesen wird.

USING <table_source>

Gibt die Datenquelle an, die mit den Datenzeilen in target_table anhand der <merge_search-Bedingung> abgeglichen wird. Das Ergebnis dieser Zuordnung legt die Aktionen fest, die von den WHEN-Klauseln der MERGE-Anweisung ausgeführt werden müssen. <table_source> kann eine Remotetabelle oder eine abgeleitete Tabelle sein, die auf Remotetabellen zugreift.

<table_source> kann eine abgeleitete Tabelle sein, die mit dem Transact-SQL-Zeilenkonstruktor (auch Tabellenwertkonstruktor genannt) mehrere Zeilen in einer einzelnen Anweisung angibt.

Weitere Informationen zur Syntax und zu den Argumenten dieser Klausel finden Sie unter FROM (Transact-SQL).

ON <merge_search_condition>

Gibt die Bedingungen an, unter denen <table_source> mit target_table verknüpft wird, um Übereinstimmungen zu ermitteln.

Vorsicht:

Es ist wichtig, dass nur die Spalten aus der Zieltabelle angegeben werden, die für Zuordnungszwecke verwendet werden. Geben Sie also Spalten aus der Zieltabelle an, die mit der entsprechenden Spalte der Quelltabelle verglichen werden. Versuchen Sie nicht, die Abfrageleistung zu optimieren, indem Sie Zeilen in der Zieltabelle in der ON-Klausel herausfiltern, beispielsweise durch Angabe von AND NOT target_table.column_x = value. Dadurch kann es zu unerwarteten und falschen Ergebnissen kommen.

WHEN MATCHED THEN <merge_matched>

Gibt an, dass alle Zeilen von target_table, die mit den durch <table_source> ON <merge_search_condition> zurückgegebenen Zeilen übereinstimmen und alle zusätzlichen Suchbedingungen erfüllen, gemäß der <merge_matched>-Klausel aktualisiert oder gelöscht werden.

Die MERGE-Anweisung kann höchstens über zwei WHEN MATCHED-Klauseln verfügen. Wenn zwei Klauseln angegeben werden, dann muss die erste Klausel von einer AND <search_condition>-Klausel begleitet werden. Für jede gegebene Zeile wird die zweite WHEN MATCHED-Klausel nur angewendet, wenn die erste nicht angewendet wurde. Wenn zwei WHEN MATCHED-Klauseln vorhanden sind, muss die eine eine UPDATE-Aktion und die andere eine DELETE-Aktion angeben. Wenn UPDATE in der <merge_matched>-Klausel angegeben wird und mehr als eine Zeile aus <table_source> gemäß <merge_search_condition> mit einer Zeile in target_table übereinstimmt, gibt SQL Server einen Fehler zurück. Die MERGE-Anweisung kann dieselbe Zeile nicht mehrmals aktualisieren oder dieselbe Zeile aktualisieren und löschen.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Gibt an, dass für jede Zeile, die durch <table_source> ON <merge_search_condition> zurückgegeben wird und nicht mit einer Zeile in target_table übereinstimmt, aber eine zusätzliche Suchbedingung erfüllt (falls vorhanden), eine Zeile in target_table eingefügt wird. Die einzufügenden Werte werden durch die <merge_not_matched>-Klausel angegeben. Die MERGE-Anweisung kann nur über eine WHEN NOT MATCHED-Klausel verfügen.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

Gibt an, dass alle Zeilen von target_table, die nicht mit den durch <table_source> ON <merge_search_condition> zurückgegebenen Zeilen übereinstimmen und die alle zusätzlichen Suchbedingungen erfüllen, gemäß der <merge_matched>-Klausel aktualisiert oder gelöscht werden.

Die MERGE-Anweisung kann höchstens über zwei WHEN NOT MATCHED BY SOURCE-Klauseln verfügen. Wenn zwei Klauseln angegeben werden, dann muss die erste Klausel von einer AND <clause_search_condition>-Klausel begleitet werden. Für jede gegebene Zeile wird die zweite WHEN NOT MATCHED BY SOURCE-Klausel nur angewendet, wenn die erste nicht angewendet wurde. Wenn zwei WHEN NOT MATCHED BY SOURCE-Klauseln vorhanden sind, muss die eine eine UPDATE-Aktion und die andere eine DELETE-Aktion angeben. In <clause_search_condition> kann nur auf Spalten aus der Zieltabelle verwiesen werden.

Wenn durch <table_source> keine Zeilen zurückgegeben werden, kann auf Spalten in der Quelltabelle nicht zugegriffen werden. Wenn die in der <merge_matched>-Klausel angegebene Aktualisierungs- oder Löschaktion auf Spalten in der Quelltabelle verweist, wird der Fehler 207 (Ungültiger Spaltenname) zurückgegeben. Die Klausel WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 kann beispielsweise dazu führen, dass für die Anweisung ein Fehler auftritt, da der Zugriff auf Col1 in der Quelltabelle nicht möglich ist.

AND <clause_search_condition>

Gibt jede gültige Suchbedingung an. Weitere Informationen finden Sie unter Suchbedingung (Transact-SQL).

<table_hint_limited>

Gibt mindestens einen Tabellenhinweis an, der für jeden durch die MERGE-Anweisung ausgeführten Einfüge-, Aktualisierungs- oder Löschvorgang auf die Zieltabelle angewendet wird. Das WITH-Schlüsselwort und die Klammern sind erforderlich.

NOLOCK und READUNCOMMITTED sind nicht zulässig. Weitere Informationen zu Tabellenhinweisen finden Sie unter Tabellenhinweise (Transact-SQL).

Das Angeben eines TABLOCK-Hinweises für eine Tabelle, die das Ziel einer INSERT-Anweisung ist, hat die gleiche Wirkung wie das Angeben eines TABLOCKX-Hinweises. Auf die Tabelle wird eine exklusive Sperre angewendet. Wenn FORCESEEK angegeben wird, wird der Hinweis auf die implizite Instanz der Zieltabelle angewendet, die mit der Quelltabelle verknüpft ist.

Vorsicht:
Wenn READPAST mit WHEN NOT MATCHED [ BY TARGET ] THEN INSERT angegeben wird, kann dies zu INSERT-Operationen führen, die gegen UNIQUE-Beschränkungen verstoßen.

INDEX ( index_val [ ,...n ] )

Gibt den Namen oder die ID eines oder mehrerer Indizes in der Zieltabelle zum Ausführen einer impliziten Verknüpfung mit der Quelltabelle an. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

<output_clause>

Gibt in keiner bestimmten Reihenfolge eine Zeile für jede Zeile in target_table zurück, die aktualisiert, eingefügt oder gelöscht wird. Weitere Informationen zu den Argumenten dieser Klausel finden Sie unter OUTPUT-Klausel (Transact-SQL).

OPTION ( <query_hint> [ ,...n ] )

Gibt an, dass zum Anpassen der Art und Weise, wie die Anweisung durch das Datenbankmodul verarbeitet wird, Hinweise des Abfrageoptimierers verwendet werden. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).

<merge_matched>

Gibt die Aktualisierungs- oder Löschaktion an, die auf alle Zeilen von target_table angewendet wird, die nicht mit den durch <table_source> ON <merge_search_condition> zurückgegebenen Zeilen übereinstimmen und die alle zusätzlichen Suchbedingungen erfüllen.

UPDATE SET <set_clause>

Gibt die Liste der Spalten- oder Variablennamen an, die in der Zieltabelle aktualisiert werden sollen, sowie die Werte, mit denen die Aktualisierung vorgenommen werden soll.

Weitere Informationen zu den Argumenten dieser Klausel finden Sie unter UPDATE (Transact-SQL). Es ist nicht zulässig, eine Variable auf denselben Wert festzulegen wie eine Spalte.

DELETE

Gibt an, dass die Zeilen, die mit Zeilen in target_table übereinstimmen, gelöscht werden.

<merge_not_matched>

Gibt die Werte an, die in die Zieltabelle eingefügt werden sollen.

( column_list )

Ist eine Liste mit einer oder mehreren Spalten der Zieltabelle, in die Daten eingefügt werden sollen. Spalten müssen als einteiliger Name angegeben werden. Andernfalls schlägt die MERGE-Anweisung fehl. column_list muss in Klammern gesetzt und durch Trennzeichen getrennt werden.

VALUES ( values_list )

Eine durch Trennzeichen getrennte Liste mit Konstanten, Variablen oder Ausdrücken, die Werte zum Einfügen in die Zieltabelle zurückgeben. Ausdrücke dürfen keine EXECUTE-Anweisung enthalten.

DEFAULT VALUES

Erzwingt, dass die eingefügte Zeile den für jede Spalte definierten Standardwert enthält.

Weitere Informationen zu dieser Klausel finden Sie unter INSERT (Transact-SQL).

<search condition>

Gibt die Suchbedingungen an, die zum Angeben von <merge_search_condition> oder <clause_search_condition> verwendet werden. Weitere Informationen zu den Argumenten für diese Klausel finden Sie unter Suchbedingung (Transact-SQL).

Mindestens eine der drei MATCHED-Klauseln muss angegeben werden, dies kann jedoch in beliebiger Reihenfolge erfolgen. Eine Variable in derselben MATCHED-Klausel kann nicht mehr als einmal aktualisiert werden.

Jede Einfüge-, Aktualisierungs- oder Löschaktion, die in der Zieltabelle durch die MERGE-Anweisung angegeben wird, ist durch alle für die Tabelle definierten Beschränkungen eingeschränkt, einschließlich aller kaskadierenden referenziellen Integritätsbeschränkungen. Wenn IGNORE_DUP_KEY für alle eindeutigen Indizes in der Zieltabelle auf ON festgelegt ist, ignoriert MERGE diese Einstellung.

Die MERGE-Anweisung erfordert ein Semikolon (;) als Abschlusszeichen für die Anweisung. Wenn eine MERGE-Anweisung ohne das Abschlusszeichen ausgeführt wird, wird der Fehler 10713 generiert.

Bei Verwendung nach MERGE gibt @@ROWCOUNT (Transact-SQL) die Gesamtanzahl der eingefügten, aktualisierten und gelöschten Zeilen an den Client zurück.

MERGE ist ein vollständig reserviertes Schlüsselwort, wenn der Kompatibilitätsgrad der Datenbank auf 100 festgelegt ist. Die MERGE-Anweisung ist sowohl bei einem Kompatibilitätsgrad von 90 als auch von 100 verfügbar. Bei einem Kompatibilitätsgrad von 90 ist das Schlüsselwort allerdings nicht vollständig reserviert.

Triggerimplementierung

Erfordert die SELECT-Berechtigung für die Quelltabelle und die INSERT-, UPDATE- oder DELETE-Berechtigung für die Zieltabelle. Weitere Informationen finden Sie im Abschnitt "Berechtigungen" unter den Themen zu SELECT, INSERT, UPDATE und DELETE.

A. Verwenden von MERGE zum Ausführen von INSERT- und UPDATE-Vorgängen für eine Tabelle in einer einzelnen Anweisung

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.    
    UPDATE Production.UnitMeasure
	SET Name = @Name
	WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.	
	IF (@@ROWCOUNT = 0 )
	BEGIN
	    INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
	    VALUES (@UnitMeasureCode, @Name)
	END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.
-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
    (ExistingCode nchar(3),
     ExistingName nvarchar(50),
     ExistingDate datetime,
     ActionTaken nvarchar(10),
     NewCode nchar(3),
     NewName nvarchar(50),
     NewDate datetime
    );
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;
           
    MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
	WHEN NOT MATCHED THEN	
	    INSERT (UnitMeasureCode, Name)
	    VALUES (source.UnitMeasureCode, source.Name)
	    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;
-- Cleanup 
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable;
Go

B. Verwenden von MERGE zum Ausführen von UPDATE- und DELETE-Vorgängen für eine Tabelle in einer einzelnen Anweisung

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. Verwenden von MERGE zum Ausführen von UPDATE- und INSERT-Vorgängen für eine Zieltabelle unter Verwendung einer abgeleiteten Quelltabelle

USE AdventureWorks;
GO
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
	UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

D. Einfügen der Ergebnisse der MERGE-Anweisung in eine andere Tabelle

USE AdventureWorks;
GO
CREATE TABLE Production.UpdatedInventory
    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty 
FROM
(    MERGE Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty) 
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO

Verweis

Andere Ressourcen

Hilfe und Informationen