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.”
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');
BusinessEntityID LastName FirstName ---------------- ------------- -------------- 7777 Jane Doe