The OUTPUT clause allows you to combine DML statements with a kind of SELECT statement on the rows affected by the DML operation. This is a powerful way to visualize what records were touched by your statement, or an easy way to build an auditing mechanism.

Syntax

An OUTPUT clause is added to an existing DML statement (INSERT, UPDATE, DELETE or MERGE). It works as if combining a SELECT statement on the affected rows of the DML statement, using the inserted and deleted special tables (as you may remember from triggers) to get to which information was changed in a DML operation. Another benefit is that you can (atomically!) retrieve IDENTITY values or calculated column values for new records, without having to requery the table again.

The syntax is really simple:

OUTPUT deleted.x AS x_before, inserted.x AS x_after

This works like a SELECT statement and returns the column x as it was before the update, and x as it is after. OUTPUT can also be used to write to another database object by adding an INTO keywork, like this:

OUTPUT deleted.x AS x_before, inserted.x AS x_after
INTO dbo.changelog_table (x_before, x_after)

Used with INSERT

In an INSERT statement, the OUTPUT clause is placed after the INSERT header, but before VALUES() or SELECT. In INSERT operations, the “deleted” table is not available. Only the “inserted” table can be used.

INSERT INTO Sales.CurrencyRate (CurrencyRateDate, FromCurrencyCode,
    ToCurrencyCode, AverageRate, EndOfDayRate)
OUTPUT inserted.CurrencyRateID
VALUES ({d '2008-07-02'}, 'USD', 'EUR', 0.9962, 0.9962);

.. or with the INSERT INTO .. SELECT pattern:

INSERT INTO Sales.CurrencyRate (CurrencyRateDate, FromCurrencyCode,
    ToCurrencyCode, AverageRate, EndOfDayRate)
OUTPUT inserted.*
SELECT DATEADD(yy, 2, CurrencyRateDate) AS CurrencyRateDate,
    FromCurrencyCode, ToCurrencyCode, AverageRate, EndOfDayRate
FROM Sales.CurrencyRate
WHERE CurrencyRateDate>={d '2007-01-01'} AND
    CurrencyRateDate<={d '2007-12-31'} AND
    FromCurrencyCode='USD' AND
    ToCurrencyCode='EUR';

Used with UPDATE

In the UPDATE statement, the OUTPUT clause is placed after the SET instructions, but before WHERE or FROM.

UPDATE Sales.CurrencyRate
SET AverageRate=AverageRate*1.01,
    EndOfDayRate=EndOfDayRate*1.01
OUTPUT deleted.CurrencyRateDate,
    deleted.AverageRate AS oldAvgRate, inserted.AverageRate AS newAvgRate,
    deleted.EndOfDayRate AS oldEodRate, inserted.EndOfDayRate AS newEodRate
WHERE CurrencyRateDate>={d '2008-01-01'} AND
    FromCurrencyCode='USD' AND
    ToCurrencyCode='EUR';

Note how both the “inserted” and “deleted” special tables are available. The “deleted” table contains each record as it was before the DML statement, the “inserted” shows the record after the statement.

Used with DELETE

Finally, in the DELETE statement, OUTPUT is placed after FROM, before WHERE:

DELETE FROM Sales.CurrencyRate
OUTPUT deleted.*
WHERE ToCurrencyCode='SAR';

With the DELETE statement, OUTPUT obviously cannot use the “inserted” special table, as there have not been any updates or inserts made to the table, only records deleted.

Using OUTPUT INTO to write changes to a table

One of the best uses for OUTPUT is when it’s used with the INTO keyword. This allows you to insert the OUTPUT recordset into a table in the database, which is perfectly for audit logs or other forms of history or backup tables.

In the following example, we’re actually atomically moving rows from one table to another, using a DELETE statement with an OUTPUT.. INTO pattern:

DELETE FROM Sales.CurrencyRate
OUTPUT deleted.*, GETDATE() AS MovedDate, SUSER_SNAME() AS MovedBy
INTO Sales.DeletedCurrencyRates (CurrencyRateID, CurrencyRateDate,
    FromCurrencyCode, ToCurrencyCode, AverageRate, EndOfDayRate,
    ModifiedDate, MovedDate, MovedBy)
WHERE ToCurrencyCode='SAR';

Note in the example how we’ve added our own auditing information in the MovedDate and MovedBy columns.

Another important distinction: OUTPUT INTO does not work like SELECT INTO – it does not create a new table, but rather inserts rows into an existing one.