Udostępnij za pośrednictwem

OUTPUT Clause (Transact-SQL)

  • Artykuł

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.

Used in:

DELETE

INSERT

UPDATE

Topic link iconTransact-SQL Syntax Conventions

Syntax

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
    [ ,...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }

Arguments

  • @table_variable
    Specifies a table variable that the returned rows are inserted into instead of being returned to the caller. @table_variable must be declared before the INSERT, UPDATE, or DELETE statement.

    If column_list is not specified, the table variable must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns, which must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

    For more information about table variables, see table (Transact-SQL).

  • output_table
    Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

    If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

    output_table cannot:

    • Have enabled triggers defined on it.
    • Participate on either side of a foreign key constraint.
    • Have CHECK constraints or enabled rules.
  • column_list
    Is an optional list of column names on the target table of the INTO clause. It is analogous to the column list allowed in the INSERT statement.

  • scalar_expression
    Is any combination of symbols and operators that evaluates to a single value. Aggregate functions are not permitted in scalar_expression.

    Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

  • column_alias_identifier
    Is an alternative name used to reference the column name.

  • DELETED
    Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE or DELETE statement is completed.

    DELETED cannot be used with the OUTPUT clause in the INSERT statement.

  • INSERTED
    Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE or INSERT statement is completed but before triggers are executed.

    INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

  • from_table_name
    Is a column prefix that specifies a table included in the FROM clause of a DELETE or UPDATE statement that is used to specify the rows to update or delete.

    If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

  • *
    Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table.

    For example, OUTPUT DELETED.* in the following DELETE statement returns all columns deleted from the ShoppingCartItem table:

    DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;
  • column_name
    Is an explicit column reference. Any reference to the table being modified must be correctly qualified by either the INSERTED or the DELETED prefix as appropriate, for example: INSERTED**.**column_name.

Remarks

The OUTPUT <dml_select_list> clause and the OUTPUT <dml_select_list> INTO { @table_variable | output_table } clause can be defined in a single INSERT, UPDATE, or DELETE statement.

Note

Unless specified otherwise, references to the OUTPUT clause refer to both the OUTPUT clause and the OUTPUT INTO clause.

The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

When a computed column is included in the <dml_select_list>, the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.

The OUTPUT clause is not supported in the following statements:

  • DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
  • INSERT statements that contain an EXECUTE statement.

The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

To prevent nondeterministic behavior, the OUTPUT clause cannot contain subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.

Triggers

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

If the sp_configure option disallow results from triggers is set, an OUTPUT clause without an INTO clause causes the statement to fail when it is invoked from within a trigger.

Data Types

The OUTPUT clause supports the large object data types: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, and xml. When you use the .WRITE clause in the UPDATE statement to modify an nvarchar(max), varchar(max), or varbinary(max) column, the full before and after images of the values are returned if they are referenced. The TEXTPTR( ) function cannot appear as part of an expression on a text, ntext, or image column in the OUTPUT clause.

Queues

You can use OUTPUT in applications that use tables as queues, or to hold intermediate result sets. That is, the application is constantly adding or removing rows from the table. The following example uses the OUTPUT clause in a DELETE statement to return the deleted row to the calling application.

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

This example removes a row from a table used as a queue and returns the deleted values to the processing application in a single action. Other semantics may also be implemented, such as using a table to implement a stack. However, SQL Server does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause. It is up to the application to include an appropriate WHERE clause that can guarantee the desired semantics, or understand that when multiple rows may qualify for the DML operation, there is no guaranteed order. The following example uses a subquery and assumes uniqueness is a characteristic of the DatabaseLogID column in order to implement the desired ordering semantics.

USE tempdb
go

CREATE TABLE table1
(
    id INT,
    employee VARCHAR(32)
)
go

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

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

PRINT 'table1, before delete' 
SELECT * FROM table1

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

PRINT 'table1, after delete'
SELECT * FROM table1

PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar

DROP TABLE table1

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

Note

Use the READPAST table hint in UPDATE and DELETE statements if your scenario allows for multiple applications to perform a destructive read from one table. This prevents locking issues that can come up if another application is already reading the first qualifying record in the table.

Permissions

SELECT permissions are required on any columns retrieved through <dml_select_list> or used in <scalar_expression>.

INSERT permissions are required on any tables specified in <output_table>.

Examples

A. Using OUTPUT INTO with a simple INSERT statement

The following example inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVartable variable. Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. However, note that the value generated by the Database Engine for that column is returned in the OUTPUT clause in the column INSERTED.ScrapReasonID.

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

B. Using OUTPUT with a DELETE statement

The following example deletes all rows in the ShoppingCartItem table. The clause OUTPUT DELETED.* specifies that the results of the DELETE statement, that is all columns in the deleted rows, be returned to the calling application. The SELECT statement that follows verifies the results of the delete operation on the ShoppingCartItem table.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

C. Using OUTPUT INTO with an UPDATE statement

The following example updates the VacationHours column in the Employee table by 25 percent for the first 10 rows. The OUTPUT clause returns the VacationHours value that exists before applying the UPDATE statement in the column DELETED.VacationHours, and the updated value in the column INSERTED.VacationHours to the @MyTableVartable variable.

Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Note the results in the INSERTED.ModifiedDate column are not the same as the values in the ModifiedDate column in the Employee table. This is because an AFTER UPDATE trigger that updates the value of ModifiedDate to the current date is defined on the Employee table. However, the columns returned from OUTPUT reflect the data before triggers are fired.

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 
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.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. Using OUTPUT INTO to return an expression

The following example builds on example C by defining an expression in the OUTPUT clause as the difference between the updated VacationHours value and the VacationHours value before the update was applied. The value of this expression is returned to the @MyTableVartable variable in the column VacationHoursDifference.

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 
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

E. Using OUTPUT INTO with from_table_name in an UPDATE statement

The following example updates the ScrapReasonID column in the WorkOrder table for all work orders with a specified ProductID and ScrapReasonID. The OUTPUT INTO clause returns values from the table being updated (WorkOrder) and also from the Product table. The Product table is used in the FROM clause to specify the rows to update. Because the WorkOrder table has an AFTER UPDATE trigger defined on it, the INTO keyword is required.

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

F. Using OUTPUT INTO with from_table_name in a DELETE statement

The following example deletes rows in the ProductProductPhoto table based on search criteria defined in the FROM clause of DELETE statement. The OUTPUT clause returns columns from the table being deleted (DELETED.ProductID, DELETED.ProductPhotoID) and columns from the Product table. This table is used in the FROM clause to specify the rows to delete.

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

G. Using OUTPUT INTO with a large object data type

The following example updates a partial value in DocumentSummary, an nvarchar(max) column in the Production.Document table, by using the .WRITE clause. The word components is replaced by the word features by specifying the replacement word, the beginning location (offset) of the word to be replaced in the existing data, and the number of characters to be replaced (length). The example uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVartable variable. Note that the full before and after images of the DocumentSummary column are returned.

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

H. Using OUTPUT in an INSTEAD OF trigger

The following example uses the OUTPUT clause in a trigger to return the results of the trigger operation. First, a view is created on the ScrapReason table, and then an INSTEAD OF INSERT trigger is defined on the view that lets only the Name column of the base table to be modified by the user. Because the column ScrapReasonID is an IDENTITY column in the base table, the trigger ignores the user-supplied value. This allows the Database Engine to automatically generate the correct value. Also, the value supplied by the user for ModifiedDate is ignored and is set to the current date. The OUTPUT clause returns the values actually inserted into the ScrapReason table.

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

Here is the result set generated on April 12, 2004 ('2004-04-12'). Notice that the ScrapReasonIDActual and ModifiedDate columns reflect the values generated by the trigger operation instead of the values provided in the INSERT statement.

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

I. Using OUTPUT INTO with identity and computed columns

The following example creates the EmployeeSales table and then inserts several rows into it using an INSERT statement with a SELECT statement to retrieve data from source tables. The EmployeeSales table contains an identity column (EmployeeID) and a computed column (ProjectedSales). Because these values are generated by the SQL Server Database Engine during the insert operation, neither of these columns can be defined in @MyTableVar.

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

J. Using OUTPUT and OUTPUT INTO in a single statement

The following example deletes rows in the ProductProductPhoto table based on search criteria defined in the FROM clause of DELETE statement. The OUTPUT INTO clause returns columns from the table being deleted (DELETED.ProductID, DELETED.ProductPhotoID) and columns from the Product table to the @MyTableVartable variable. The Product table is used in the FROM clause to specify the rows to delete. The OUTPUT clause returns the DELETED.ProductID, DELETED.ProductPhotoID columns and the date and time the row was deleted from the ProductProductPhoto table to the calling application.

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

See Also

Reference

DELETE (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
table (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
sp_configure (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
Changed content:
  • Removed inaccurate content about using subqueries in scalar_expression. To avoid inconsistent row results, subqueries in an OUTPUT clause are no longer allowed.