SQL Server Documentation

OUTPUT Clause (Transact-SQL)

The OUTPUT clause in Transact-SQL allows you to return information from, or the effect of, each data modification statement in a DML statement. This includes rows affected by an INSERT, UPDATE, DELETE, or MERGE statement.

The OUTPUT clause can return the old or new values of one or more columns, or the result of an expression. The returned rows are sent back to the client application as a result set.

Syntax

<dml_statement>
    [ OUTPUT
        { column << column | <expression> } [,...n]
        [ INTO { <table_variable> | <user_defined_table_type> | <table> [ ( <column_list> ) ] }
          [ <table_hint_array> [,...n] ]
    ]
<dml_statement> ::=
    INSERT { <table_or_view> }
    { <values_list> | <select_statement> | <table_constructor >}
    | UPDATE { <table_or_view> }
    { SET <column_equal_expression_list> }
    | DELETE [ FROM ] { <table_or_view> }
    | MERGE [ <table_or_view> AS <target_ DML>
        USING { <table_or_view> | <query> | <function> } AS <source_ DML>
        ...
    ]

Parameters

• column << column: Specifies the name of a column in the affected table. The value of this column is returned.
• << expression: Specifies an expression that is evaluated and returned. The expression can reference columns from the affected table.
• INTO <table_variable> | <user_defined_table_type> | <table>: If specified, the results of the OUTPUT clause are inserted into the specified table variable, user-defined table type, or table instead of being returned to the client.
• <column_list>: A comma-separated list of columns in the target table or table variable to insert the output into.

Special Identifiers

When using the OUTPUT clause, you can use special identifiers to refer to the state of the rows:

• inserted: Refers to the new row values after the DML operation.
• deleted: Refers to the old row values before the DML operation.

Examples

Example 1: Returning inserted rows from an INSERT statement

This example demonstrates how to use the OUTPUT clause to return the values of the newly inserted rows into a table variable.

-- Assume 'Products' table exists with columns: ProductID (INT IDENTITY), ProductName (VARCHAR(50)), Price (DECIMAL(10, 2))

DECLARE @OutputTable TABLE (ProductID INT, ProductName VARCHAR(50));

INSERT INTO Products (ProductName, Price)
OUTPUT inserted.ProductID, inserted.ProductName
INTO @OutputTable (ProductID, ProductName)
VALUES ('Laptop', 1200.00), ('Keyboard', 75.00);

SELECT * FROM @OutputTable;
/*
Output:
ProductID   ProductName
----------- -----------
1           Laptop
2           Keyboard
*/

SELECT * FROM Products;
/*
Output:
ProductID   ProductName  Price
----------- ------------ -------
1           Laptop       1200.00
2           Keyboard     75.00
*/
                

Example 2: Returning changed values from an UPDATE statement

This example shows how to capture the old and new values of a column after an UPDATE operation.

-- Assume 'Employees' table exists with columns: EmployeeID (INT IDENTITY), EmployeeName (VARCHAR(100)), Salary (DECIMAL(10, 2))

DECLARE @UpdatedEmployees TABLE (
    EmployeeID INT,
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2)
);

UPDATE Employees
SET Salary = Salary * 1.10 -- Increase salary by 10%
OUTPUT deleted.EmployeeID, deleted.Salary, inserted.Salary
INTO @UpdatedEmployees (EmployeeID, OldSalary, NewSalary)
WHERE EmployeeID IN (1, 3);

SELECT * FROM @UpdatedEmployees;
/*
Output (assuming EmployeeID 1 and 3 existed with salaries):
EmployeeID  OldSalary  NewSalary
----------  ---------  ---------
1           50000.00   55000.00
3           60000.00   66000.00
*/
                

Example 3: Using OUTPUT with MERGE

The OUTPUT clause can be used with MERGE to track which rows were inserted, updated, or deleted.

-- Assume 'TargetTable' and 'SourceTable' exist

-- Create dummy tables for demonstration
IF OBJECT_ID('TargetTable', 'U') IS NOT NULL DROP TABLE TargetTable;
CREATE TABLE TargetTable (ID INT PRIMARY KEY, Value VARCHAR(50));
INSERT INTO TargetTable (ID, Value) VALUES (1, 'One'), (2, 'Two');

IF OBJECT_ID('SourceTable', 'U') IS NOT NULL DROP TABLE SourceTable;
CREATE TABLE SourceTable (ID INT PRIMARY KEY, Value VARCHAR(50));
INSERT INTO SourceTable (ID, Value) VALUES (2, 'Two Updated'), (3, 'Three');

-- Table to capture merge results
DECLARE @MergeResults TABLE (Action VARCHAR(10), TargetID INT, TargetValue VARCHAR(50), SourceID INT, SourceValue VARCHAR(50));

MERGE TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Value = S.Value
    OUTPUT 'UPDATE', T.ID, T.Value, S.ID, S.Value
    INTO @MergeResults (Action, TargetID, TargetValue, SourceID, SourceValue)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Value) VALUES (S.ID, S.Value)
    OUTPUT 'INSERT', NULL, NULL, S.ID, S.Value
    INTO @MergeResults (Action, TargetID, TargetValue, SourceID, SourceValue)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
    OUTPUT 'DELETE', T.ID, T.Value, NULL, NULL
    INTO @MergeResults (Action, TargetID, TargetValue, SourceID, SourceValue);

SELECT * FROM @MergeResults;
/*
Output:
Action   TargetID  TargetValue  SourceID  SourceValue
-------- --------- ------------ --------- ------------
UPDATE   2         Two Updated  2         Two Updated
INSERT   NULL      NULL         3         Three
*/

SELECT * FROM TargetTable;
/*
Output:
ID   Value
---- ---------------
1    One
2    Two Updated
3    Three
*/

DROP TABLE TargetTable;
DROP TABLE SourceTable;
                

Considerations

• The OUTPUT clause can be used with INSERT, UPDATE, DELETE, and MERGE statements.
• When using OUTPUT INTO, the target table or table variable must exist before the statement is executed.
• The order of columns returned by OUTPUT when inserted into a table variable is the order specified in the OUTPUT clause.
• The OUTPUT clause cannot be used with SELECT INTO statements.
• Be mindful of performance implications, especially with large result sets.