Microsoft® SQL Server® 2012 Bible

317

Chapter 12: Modifying Data In SQL Server

12

Result:

Passenger previous seat final seat

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

Jerry Nixon 29B 2A

For another explanation of set difference queries, fl ip to Chapter 9, “Merging Data with Joins, Subqueries, and CTEs.”

With the scenario’s data in place and verifi ed with set-difference queries, it’s time to merge

the check-in data into the FlightPassenger table.

The fi rst section of the merge query identifi es the target and source tables and how they

relate. Following the table defi nition, there’s an optional clause for each match combina-

tion, as shown in this simplifi ed syntax:

MERGE TargetTable

USING SourceTable

ON join conditions

[WHEN Matched

THEN DML]

[WHEN NOT MATCHED BY TARGET

THEN DML]

[WHEN NOT MATCHED BY SOURCE

THEN DML]

Applying the MERGE command to the airline check-in scenario, there’s an appropriate

action for each match combination:

■ (^) If the row is in both FlightPassengers (the target) and CheckIn (the source),
then the target is updated with the CheckIn table’s seat column.
■ (^) If the row is present in CheckIn (the source) but there’s no match in
FlightPassenger (the target), then the row from CheckIn is inserted into
FlightPassenger. The data from the source table is gathered by the INSERT
command using INSERT...VALUES.
■ (^) If the row is present in FlightPassenger (the target), but there’s no match
in CheckIn (the source), then the row is deleted from FlightPassenger. The
DELETE command deletes from the target and does not require a WHERE clause
because the rows are fi ltered by the MERGE command.
Following is the complete working MERGE command for the scenario:
MERGE FlightPassengers F
USING CheckIn C
c12.indd 317c12.indd 317 7/30/2012 4:42:42 PM7/30/2012 4:42:42 PM
http://www.it-ebooks.info