Microsoft® SQL Server® 2012 Bible

320

Part II: Building Databases and Working with Data

Result:

OldFirstName OldLastName NewFirstName NewLastName

----------------- ---------------- --------------- ---------------

Doe Jane Jane Doe

Returning Data from a Delete

When deleting data, only the deleted table has any useful data to return:

Use AdventureWorks

Go

DELETE FROM PersonList

OUTPUT DELETED.*

WHERE BusinessEntityID = 77777

Result:

BusinessEntityID LastName FirstName

---------------- ------------ --------------

77777 Doe Jane

Returning Data from a Merge

The MERGE command can return data using the OUTPUT clause as well. A twist is that

the MERGE command adds a column, $action, to identify whether the row was inserted,

updated, or deleted from the target table. The next query adds the OUTPUT clause to the

previous MERGE command:

MERGE FlightPassengers F

USING CheckIn C

ON C.LastName = F.LastName

AND C.FirstName = F.FirstName

AND C.FlightCode = F.FlightCode

AND C.FlightDate = F.FlightDate

WHEN MATCHED

THEN UPDATE

SET F.Seat = C.Seat

WHEN NOT MATCHED BY TARGET

THEN INSERT (FirstName, LastName, FlightCode, FlightDate, Seat)

VALUES (FirstName, LastName, FlightCode, FlightDate, Seat)

WHEN NOT MATCHED BY SOURCE

THEN DELETE

OUTPUT

deleted.FlightID, deleted.LastName, Deleted.Seat,

$action,

inserted.FlightID, inserted.LastName, inserted.Seat ;

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

http://www.it-ebooks.info