OUTPUT Clause

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