OUTPUT-Klausel (Transact-SQL)

Wenn Sie die Ergebnisse einer OUTPUT-Klausel in einer geschachtelten INSERT-, UPDATE-, DELETE- oder MERGE-Anweisung aufzeichnen und diese Ergebnisse in eine Zieltabelle einfügen, müssen Sie Folgendes beachten:

• Der ganze Vorgang ist unteilbar. Entweder werden sowohl die äußere INSERT-Anweisung als auch die geschachtelte DML-Anweisung mit der OUTPUT-Klausel ausgeführt, oder die gesamte Anweisung schlägt fehl.

• Die folgenden Einschränkungen gelten für das Ziel der äußeren INSERT-Anweisung:

  • Das Ziel darf keine Remotetabelle, keine Remotesicht und kein allgemeiner Tabellenausdruck sein.

  • Das Ziel darf keine FOREIGN KEY-Einschränkung aufweisen, und es darf nicht durch eine FOREIGN KEY-Einschränkung darauf verwiesen werden.

  • Trigger können für das Ziel nicht definiert werden.

  • Das Ziel darf nicht Teil von Mergereplikationen oder aktualisierbaren Abonnements für Transaktionsreplikationen sein.

• Die folgenden Einschränkungen gelten für die geschachtelte DML-Anweisung:

  • Das Ziel darf keine Remotetabelle und keine partitionierte Sicht sein.

  • Die Quelle selbst darf keine <dml_table_source>-Klausel enthalten.

• Die OUTPUT INTO-Klausel wird nicht in INSERT-Anweisungen unterstützt, die eine <dml_table_source>-Klausel enthalten.

• @@ROWCOUNT gibt nur die Zeilen zurück, die von der äußeren INSERT-Anweisung eingefügt wurden.

• @@IDENTITY, SCOPE_IDENTITY und IDENT_CURRENT geben nur die von der geschachtelten DML-Anweisung generierten und nicht die von der äußeren INSERT-Anweisung generierten Identitätswerte zurück.

• In Abfragebenachrichtigungen gilt die Anweisung als einzelne Entität, und der Typ aller erstellten Benachrichtigungen wird auf den Typ der geschachtelten DML-Anweisung festgelegt, selbst wenn die signifikante Änderung von der äußeren INSERT-Anweisung stammt.

• In der <dml_table_source>-Klausel dürfen die SELECT- und die WHERE-Klausel keine Unterabfragen, Aggregatfunktionen, Rangfolgefunktionen, Prädikate der Volltextsuche, benutzerdefinierten Funktionen für einen Datenzugriff und keine TEXTPTR-Funktion enthalten.

Von OUTPUT zurückgegebene Spalten spiegeln die Daten nach Abschluss der INSERT-, UPDATE- oder DELETE-Anweisung wider, jedoch vor der Ausführung der Trigger.

Bei INSTEAD OF-Triggern werden die zurückgegebenen Ergebnisse so generiert, als ob die INSERT-, UPDATE- oder DELETE-Anweisung tatsächlich stattgefunden hat, selbst wenn keine Änderungen als Folge des Triggervorgangs vorgenommen werden. Wird eine Anweisung, die eine OUTPUT-Klausel enthält, innerhalb eines Triggers verwendet, müssen Tabellenaliase zum Verweisen auf die Tabellen inserted und deleted des Triggers verwendet werden, um das Duplizieren von Spaltenverweisen mithilfe der OUTPUT zugeordneten Tabellen INSERTED und DELETED zu vermeiden.

Wird die OUTPUT-Klausel angegeben, ohne auch das INTO-Schlüsselwort anzugeben, kann für das Ziel des DML-Vorgangs kein aktivierter Trigger für die bestimmte DML-Aktion definiert werden. Wird beispielsweise die OUTPUT-Klausel in einer UPDATE-Anweisung definiert, können keine aktivierten UPDATE-Trigger für die Zieltabelle festgelegt werden.

Wird die sp_configure-Option disallow results from triggers festgelegt, hat eine OUTPUT-Klausel ohne INTO-Klausel zur Folge, dass die Anweisung fehlschlägt, wenn sie aus einem Trigger heraus aufgerufen wird.

Die OUTPUT-Klausel unterstützt die großen Objektdatentypen nvarchar(max), varchar(max), varbinary(max), text, ntext, image und xml. Wird die .WRITE-Klausel in der UPDATE-Anweisung zum Ändern einer nvarchar(max)-, varchar(max)- oder varbinary(max)-Spalte verwendet, werden die vollständigen Anfangs- und Endabbilder der Werte zurückgegeben, wenn auf sie verwiesen wird. Die TEXTPTR( )-Funktion kann nicht im Rahmen eines Ausdrucks in einer text-, ntext- oder image-Spalte in der OUTPUT-Klausel angezeigt werden.

OUTPUT kann in Anwendungen, die Tabellen als Warteschlangen verwenden, oder zum Aufbewahren von Zwischenresultsets verwendet werden. Die Anwendung fügt der Tabelle somit kontinuierlich Zeilen hinzu oder entfernt sie daraus. Im folgenden Beispiel wird die OUTPUT-Klausel in einer DELETE-Anweisung verwendet, um die gelöschte Zeile an die aufrufende Anwendung zurückzugeben.

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

In diesem Beispiel wird eine Zeile aus einer als Warteschlange verwendeten Tabelle entfernt und die gelöschten Werte in einer einzigen Aktion an die verarbeitende Anwendung zurückgegeben. Zusätzliche Semantik kann ebenfalls implementiert werden, wie z. B. die Verwendung einer Tabelle zum Implementieren eines Stapels. SQL Server stellt jedoch die Reihenfolge nicht sicher, in der Zeilen verarbeitet und von DML-Anweisungen mithilfe der OUTPUT-Klausel zurückgegeben werden. Es hängt von der Anwendung ab, eine entsprechende WHERE-Klausel einzufügen, die die gewünschte Semantik sicherstellen kann. Wenn mehrere Zeilen Anspruch auf den DML-Vorgang haben, kann keine bestimmte Reihenfolge sichergestellt werden. Im folgenden Beispiel wird eine Unterabfrage verwendet und davon ausgegangen, dass die DatabaseLogID-Spalte eindeutig ist, um die gewünschte Reihenfolgensemantik zu implementieren.

USE tempdb;
GO

CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
);
GO

INSERT INTO dbo.table1 VALUES
    (1, 'Fred'), (2, 'Tom')
   ,(3, 'Sally'),(4, 'Alice');
GO

DECLARE @MyTableVar TABLE
(
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete' ;
SELECT id, employee FROM dbo.table1;

DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;

PRINT 'table1, after delete';
SELECT id, employee FROM table1;

PRINT '@MyTableVar, after delete';
SELECT id, employee FROM @MyTableVar;

DROP TABLE dbo.table1;

--Results
--table1, before delete
--id          employee
------------- ------------------------------
--1           Fred
--2           Tom
--3           Sally
--4           Alice
--
--table1 results, after delete
--id          employee
------------- ------------------------------
--1           Fred
--3           Sally
--
--@MyTableVar results, after delete
--id          employee
------------- ------------------------------
--2           Tom
--4           Alice

Hinweis
Verwenden Sie den READPAST-Tabellenhinweis in UPDATE- und DELETE-Anweisungen, wenn es in Ihrem Szenario möglich ist, dass mehrere Anwendungen einen destruktiven Lesevorgang aus einer Tabelle ausführen. So werden Sperrkonflikte verhindert, die entstehen, wenn eine andere Anwendung bereits den ersten berechtigten Datensatz in der Tabelle liest.

Im folgenden Beispiel wird eine Zeile in die ScrapReason-Tabelle eingefügt, und die OUTPUT-Klausel wird verwendet, um die Ergebnisse der Anweisung an die @MyTableVartable-Variable zurückzugeben. Da die ScrapReasonID-Spalte mit einer IDENTITY-Eigenschaft definiert wurde, wird kein Wert in der INSERT-Anweisung für diese Spalte angegeben. Der von Database Engine (Datenbankmodul) für diese Spalte generierte Wert wird jedoch in der OUTPUT-Klausel in der INSERTED.ScrapReasonID-Spalte zurückgegeben.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

Im folgenden Beispiel werden alle Zeilen in der ShoppingCartItem-Tabelle gelöscht. Die OUTPUT DELETED.*-Klausel gibt an, dass die Ergebnisse der DELETE-Anweisung, also alle Spalten in den gelöschten Zeilen, an die aufrufende Anweisung zurückgegeben werden. Die nachfolgende SELECT-Anweisung überprüft die Ergebnisse des Löschvorgangs in der ShoppingCartItem-Tabelle.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify all rows in the table that match the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

Im folgenden Beispiel werden die ersten zehn Zeilen der VacationHours-Spalte in der Employee-Tabelle um 25 % aktualisiert. Außerdem wird der Wert in der ModifiedDate-Spalte auf das aktuelle Datum festgelegt. Die OUTPUT-Klausel gibt den VacationHours-Wert vor dem Anwenden der UPDATE-Anweisung in der deleted.VacationHours-Spalte sowie den aktualisierten Wert in der inserted.VacationHours-Spalte an die @MyTableVartable-Variable zurück.

Danach folgen zwei SELECT-Anweisungen, die die Werte in @MyTableVar zurückgeben, sowie die Ergebnisse des Aktualisierungsvorgangs in der Employee-Tabelle.

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.EmployeeID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO

--Display the result set of the table.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Das folgende Beispiel baut auf Beispiel C auf, indem ein Ausdruck in der OUTPUT-Klausel definiert wird, der die Differenz zwischen dem aktualisierten VacationHours-Wert und dem VacationHours-Wert vor der Aktualisierung beschreibt. Der Wert dieses Ausdrucks wird an die @MyTableVartable-Variable in der VacationHoursDifference-Spalte zurückgegeben.

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    VacationHoursDifference int,
    ModifiedDate datetime);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.EmployeeID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.VacationHours - deleted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, 
    VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO

SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Im folgenden Beispiel werden alle Arbeitsaufträge in der ScrapReasonID -Spalte der WorkOrder-Tabelle aktualisiert, für die ProductID und ScrapReasonID angegeben wurde. Die OUTPUT INTO-Klausel gibt Werte aus der Tabelle, die aktualisiert wird (WorkOrder), sowie aus der Product-Tabelle zurück. Die Product-Tabelle wird in der FROM-Klausel zur Angabe der zu aktualisierenden Zeilen verwendet. Da für die WorkOrder-Tabelle ein AFTER UPDATE-Trigger definiert wurde, wird das INTO-Schlüsselwort benötigt.

USE AdventureWorks;
GO
DECLARE @MyTestVar table (
    OldScrapReasonID int NOT NULL, 
    NewScrapReasonID int NOT NULL, 
    WorkOrderID int NOT NULL,
    ProductID int NOT NULL,
    ProductName nvarchar(50)NOT NULL);

UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT deleted.ScrapReasonID,
       inserted.ScrapReasonID, 
       inserted.WorkOrderID,
       inserted.ProductID,
       p.Name
    INTO @MyTestVar
FROM Production.WorkOrder AS wo
    INNER JOIN Production.Product AS p 
    ON wo.ProductID = p.ProductID 
    AND wo.ScrapReasonID= 16
    AND p.ProductID = 733;

SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID, 
    ProductID, ProductName 
FROM @MyTestVar;
GO

Im folgenden Beispiel werden Zeilen in der ProductProductPhoto-Tabelle auf der Grundlage von in der FROM-Klausel der DELETE-Anweisung definierten Suchkriterien gelöscht. Die OUTPUT-Klausel gibt Spalten aus der Tabelle zurück, die gelöscht wird (DELETED.ProductID, DELETED.ProductPhotoID), sowie Spalten aus der Product-Tabelle. Diese Tabelle wird in der FROM-Klausel zur Angabe der zu löschenden Zeilen verwendet.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO

Im folgenden Beispiel wird ein Teilwert in DocumentSummary, eine nvarchar(max)-Spalte in der Production.Document-Tabelle, mithilfe der .WRITE-Klausel aktualisiert. Der Begriff components wird durch den Begriff features ersetzt, indem der Ersatzbegriff, die Anfangsposition (offset) des zu ersetzenden Begriffs in den vorhandenen Daten und die Anzahl der zu ersetzenden Zeichen (length) angegeben werden. Im Beispiel wird die OUTPUT-Klausel zur Rückgabe der Anfangs- und Endabbilder der DocumentSummary -Spalte an die @MyTableVartable-Variable verwendet. Hierbei werden die vollständigen Anfangs- und Endabbilder der DocumentSummary-Spalte zurückgegeben.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    DocumentID int NOT NULL,
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT inserted.DocumentID,
       deleted.DocumentSummary, 
       inserted.DocumentSummary 
    INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter 
FROM @MyTableVar;
GO

Im folgenden Beispiel werden die Ergebnisse des Triggervorgangs mithilfe der OUTPUT-Klausel in einem Trigger zurückgegeben. Zunächst wird eine Sicht in der ScrapReason-Tabelle erstellt und anschließend ein INSTEAD OF INSERT-Trigger für die Sicht definiert, der nur zulässt, dass Benutzer die Name-Spalte der Basistabelle ändern können. Da die ScrapReasonID-Spalte eine IDENTITY-Spalte in der Basistabelle ist, ignoriert der Trigger den vom Benutzer bereitgestellten Wert. Dadurch kann Database Engine (Datenbankmodul) automatisch den richtigen Wert generieren. Darüber hinaus wird der vom Benutzer für ModifiedDate bereitgestellte Wert ignoriert und auf das aktuelle Datum festgelegt. Die OUTPUT-Klausel gibt die tatsächlich in die ScrapReason-Tabelle eingefügten Werte zurück.

USE AdventureWorks;
GO
IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL
    DROP VIEW dbo.vw_ScrapReason;
GO
CREATE VIEW dbo.vw_ScrapReason
AS (SELECT ScrapReasonID, Name, ModifiedDate
    FROM Production.ScrapReason);
GO
CREATE TRIGGER dbo.io_ScrapReason 
    ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
--ScrapReasonID is not specified in the list of columns to be inserted 
--because it is an IDENTITY column.
    INSERT INTO Production.ScrapReason (Name, ModifiedDate)
        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, 
               INSERTED.ModifiedDate
    SELECT Name, getdate()
    FROM inserted;
END
GO
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
VALUES (99, N'My scrap reason','20030404');
GO

Dies ist das am 12. April 2004 ('2004-04-12') generierte Resultset. Die Spalten ScrapReasonIDActual und ModifiedDate spiegeln die vom Triggervorgang generierten Werte wider, anstelle der von der INSERT-Anweisung bereitgestellten Werte.

ScrapReasonID  Name             ModifiedDate

-------------  ---------------- -----------------------

17             My scrap reason  2004-04-12 16:23:33.050

Im folgenden Beispiel wird die EmployeeSales-Tabelle erstellt, und anschließend werden mehrere Zeilen unter Verwendung einer INSERT-Anweisung mit einer SELECT-Anweisung in die Tabelle eingefügt, um Daten aus Quelltabellen abzurufen. Die EmployeeSales-Tabelle enthält eine Identitätsspalte (EmployeeID) sowie eine berechnete Spalte (ProjectedSales). Da diese Werte während des Einfügevorgangs von SQL Server Database Engine (Datenbankmodul) generiert werden, kann keine dieser Spalten in @MyTableVar definiert werden.

USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

Im folgenden Beispiel werden Zeilen in der ProductProductPhoto-Tabelle auf der Grundlage von in der FROM-Klausel der DELETE-Anweisung definierten Suchkriterien gelöscht. Die OUTPUT INTO-Klausel gibt Spalten aus der Tabelle, die gelöscht wird (deleted.ProductID, deleted.ProductPhotoID), sowie Spalten aus der Product-Tabelle an die @MyTableVartable-Variable zurück. Die Product-Tabelle wird in der FROM-Klausel zur Angabe der zu löschenden Zeilen verwendet. Die OUTPUT-Klausel gibt die Spalten deleted.ProductID und deleted.ProductPhotoID sowie das Datum und die Uhrzeit des Löschens der Zeile aus der ProductProductPhoto-Tabelle an die aufrufende Anwendung zurück.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate 
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
WHERE p.ProductID BETWEEN 800 and 810;

--Display the results of the table variable.
SELECT ProductID, ProductName, PhotoID, ProductModelID 
FROM @MyTableVar;
GO

Im folgenden Beispiel werden von der OUTPUT-Klausel einer MERGE-Anweisung zurückgegebene Daten erfasst und in eine andere Tabelle eingefügt. Die MERGE-Anweisung aktualisiert die Quantity-Spalte der ProductInventory-Tabelle täglich auf Grundlage der Bestellungen, die in der SalesOrderDetail-Tabelle verarbeitet werden. Außerdem werden die Zeilen für Produkte gelöscht, deren Bestand auf 0 oder darunter fällt. In diesem Beispiel werden die gelöschten Zeilen erfasst und in eine andere Tabelle (ZeroInventory) eingefügt, in der Produkte ohne Bestand gespeichert werden.

USE AdventureWorks;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (ProductID int);
GO

INSERT INTO Production.ZeroInventory (ProductID)
SELECT ProductID
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 = '20030401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
GO
SELECT ProductID FROM Production.ZeroInventory;