O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

referenceaero 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required