SQL Server 2005 OUTPUT operator

  An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

<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 }
    | $action

http://msdn.microsoft.com/en-us/library/ms177564.aspx 

Let’s play with the new OUTPUT operator!

-- Setup TableA & TableB

CREATE TABLE          #TableA

                      (

                                 i INT

                      )

CREATE TABLE          #TableB

                      (

                                 i INT

                      )

CREATE TABLE          #TableC

                      (

                                 iOld INT,

                                 iNew INT

                      )

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Insert into TableA

INSERT     #TableA

OUTPUT     inserted.i

INTO       #TableB

SELECT     1 UNION ALL

SELECT     2 UNION ALL

SELECT     3

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Delete from TableA

DELETE     a

OUTPUT     10 * deleted.i + 49

INTO       #TableB

FROM       #TableA AS a

WHERE      i = 2

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Update TableB

UPDATE     b

SET        b.i = 1000 + b.i

OUTPUT     deleted.i AS [Old i value],

           inserted.i AS [New i value]

FROM       #TableB AS b

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Update TableB again

UPDATE     b

SET        b.i = b.i - 500

OUTPUT     deleted.i AS [Old i value],

           inserted.i AS [New i value]

INTO       #TableC

FROM       #TableB AS b

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Check TableC

SELECT 'C' AS [Table], * FROM #TableC

-- Clean up

DROP TABLE #TableA,

           #TableB,

           #TableC