Sql server® 2012 t-sql fundamentals

The OUTPUT Clause

Normally, you would not expect a modification statement to do more than modify data. That is, you 

would not expect a modification statement to return any output. However, in some scenarios, being 

able to get data back from the modified rows can be useful. For example, think about the advantages 

of requesting an UPDATE statement to not only modify data, but to also return the old and new val-

ues of the updated columns. This can be useful for troubleshooting, auditing, and other purposes.

SQL Server supports this capability via a clause called OUTPUT that you add to the modification 

statement. In this OUTPUT clause, you specify the attributes and expressions that you want to return 

from the modified rows. 

You can think of the OUTPUT clause in terms very similar to those you use to think about the SELECT 

clause. That is, you list the attributes and expressions based on existing attributes that you want to 

return. What’s special in terms of the OUTPUT clause syntax is that you need to prefix the attribute 

names with either the inserted or the deleted keyword. In an INSERT statement, you refer to inserted; 

in a DELETE statement, you refer to deleted; and in an UPDATE statement, you refer to deleted when 

you’re after the image of the row before the change and inserted when you’re after the image of the 

row after the change.

The OUTPUT clause will return the requested attributes from the modified rows as a result set, very 

much like a SELECT statement does. If you want to direct the result set to a table, add an INTO clause 

with the target table name. If you want to return modified rows back to the caller and also direct a 

copy to a table, specify two OUTPUT clauses—one with the INTO clause and one without it.

The following sections provide examples of using the OUTPUT clause with the different modifica-

tion statements.