Microsoft® SQL Server® 2012 Bible

321

Chapter 12: Modifying Data In SQL Server

12

Result:

FlightID LastName Seat $action FlightID LastName Seat

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

NULL NULL NULL INSERT 5 Anderson 4B

1 LeBlanc 9F UPDATE 1 LeBlanc 9F

2 Jenkins 7A UPDATE 2 Jenkins 7A

3 Smith 2A DELETE NULL NULL NULL

4 Nixon 29B UPDATE 4 Nixon 2A

Returning Data into a Table

For T-SQL developers, the OUTPUT clause can return the data for use within a batch or

stored procedure. The data is received into a user table, temp table, or table variable, which

must already have been created. Although the syntax may seem similar to the

INSERT...INTO syntax, it actually functions differently.

In the following example, the OUTPUT clause passes the results to a @DeletedPerson

table variable:

DECLARE @DeletedPerson TABLE (

BusinessEntityID INT NOT NULL PRIMARY KEY,

LastName VARCHAR(50) NOT NULL,

FirstName VARCHAR(50) NOT NULL

);

DELETE dbo.PersonList

OUTPUT Deleted.BusinessEntityID, Deleted.LastName,

Deleted.FirstName

INTO @DeletedPerson

WHERE BusinessEntityID = 2;

Interim result:

(1 row(s) affected)

Continuing the batch...

SELECT BusinessEntityID, LastName, FirstName FROM @DeletePerson;

Result:

(1 row(s) affected)

BusinessEntityID LastName FirstName

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

64 Zwilling Michael

An advance use of the OUTPUT clause, called composable DML, passes the output data to an outer

query, which can then be used in an INSERT command. For more details, refer to Chapter 9, “Merging

Data with Joins, Subqueries, and CTEs.”

c12.indd 321c12.indd 321 7/30/2012 4:42:44 PM7/30/2012 4:42:44 PM

http://www.it-ebooks.info