Microsoft® SQL Server® 2012 Bible

319

Chapter 12: Modifying Data In SQL Server

12

For more examples of the inserted and deleted table, turn to Chapter 18, “Building User-Defi ned

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 cre-

ated surrogate keys. The identity_scope() function returns the last single identity inserted, but it

can’t return a set of new identity values. There is no function to return the GUID value just created by a

newsequentialid() default. However, the OUTPUT clause returns sets of new surrogate keys regard-

less of their data type. You can almost think of the INSERT...OUTPUT as a scope_GUID() function or

a set-based scope_identity().

Returning Data from an Update

The OUTPUT clause also works with updates and can return the before and after picture

of the data. In this example, the deleted virtual table is used to grab the original value,

whereas the inserted virtual table stores the new updated value. Only the Qualifications

column is returned:

use AdventureWorks;

UPDATE PersonList

SET FirstName = 'Jane', LastName = 'Doe'

OUTPUT Deleted.FirstName OldFirstName,Deleted.LastName OldLastName,

Inserted.FirstName NewFirstName, Inserted.LastName NewLastName

WHERE BusinessEntityID = 77777

c12.indd 319c12.indd 319 7/30/2012 4:42:43 PM7/30/2012 4:42:43 PM

http://www.it-ebooks.info