Microsoft® SQL Server® 2012 Bible

318

Part II: Building Databases and Working with Data

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 ;

The next query looks at the results of the MERGE command, returning the fi nalized passen-

ger list for SQL Server Airlines fl ight 2008:

SELECT FlightID, FirstName, LastName, FlightCode, FlightDate, Seat

FROM FlightPassengers

Result:

FlightID FirstName LastName FlightCode FlightDate Seat

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

1 Patrick LeBlanc SS2008 2009-03-01 9F

2 Sue Jenkins SS2008 2009-03-01 7A

4 Jerry Nixon SS2008 2009-03-01 2A

5 Missy Anderson SS2008 2009-03-01 4B

MERGE has a few specifi c rules:

■ (^) It must be terminated by a semicolon.
■ (^) The rows must match one-to-one. One-to-many matches are not permitted.
■ (^) The join conditions must be deterministic, meaning they are repeatable.

Returning Modifi ed Data

SQL Server can optionally return the modifi ed data as a data set for further use. This can

be useful to perform more work on the modifi ed 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.

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

http://www.it-ebooks.info