Microsoft SQL Server 2012 Bible

Returning Modified Data

SQL Server can optionally return the modified data as a data set for further use. This can be useful to perform more work on the modified data, or to return the data to the front-end application to eliminate an extra round-trip to the server.

The OUTPUT clause can access the inserted and deleted virtual tables, as well as any data source referenced in the FROM clause, to select the data to be returned. Normally used only by triggers, inserted and deleted virtual tables contain the before and after views to the transaction. The deleted virtual table stores the old data, and the inserted virtual table stores the newly inserted or updated data.

For more examples of the inserted and deleted table, turn to Chapter 18, “Building User-Defined Functions.”

Returning Data from an Insert

The INSERT command makes the inserted virtual table available. The following example, taken from earlier in this chapter, has been edited to include the OUTPUT clause. The inserted virtual table has a picture of the new data being inserted and returns the data:

USE AdventureWorks
GO
INSERT INTO PersonList
OUTPUT Inserted.*
VALUES(77777, ‘Jane', ‘Doe');

Result:

BusinessEntityID  LastName   FirstName
----------------  ------------- --------------
7777        Jane     Doe

Best Practice

An excellent application of the OUTPUT clause within an INSERT is returning the values of newly created surrogate ...