Return information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement.
Syntax
{
[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}
Key:
@table_variable a table variable that the returned rows are inserted into instead of being returned to the caller.
output_table A table that the returned rows are inserted into instead of being returned to the caller.
column_list An optional list of column names on the target table of the INTO clause.
scalar_expression An expression that evaluates to a single value.
DELETED A column prefix that specifies the value deleted by the update/delete.
INSERTED A column prefix that specifies the value added by the insert/update.
If multiple users may be performing a destructive read from one table use the READPAST table hint to prevent locking issues.
Example
DELETE dbo.MyTable WITH (READPAST)
OUTPUT deleted.*
WHERE DbID = 100;
GO
"If you can suffer without a hint of self-pity, without a hint of self-preoccupation, then this develops an almost limitless capacity for compassion for everyone everywhere" ~ John Griffin
Related commands:
INSERT
UPDATE
DELETE
SET TRANSACTION ISOLATION LEVEL