Microsoft® SQL Server® 2012 Bible

315

Chapter 12: Modifying Data In SQL Server

12

Seat CHAR(3) NOT NULL

);

INSERT FlightPassengers

(LastName, FirstName, FlightCode, FlightDate, Seat)

VALUES ('LeBlanc, 'Patrick, 'SS2008', '20090301', '9F'),

('Jenkins', 'Sue', 'SS2008', '20090301', '7A'),

('Smith', 'Sam', 'SS2008', '20090301', '19A'),

('Nixon', 'Jerry', 'SS2008', '20090301', '29B');

The day of the fl ight, the check-in counter records all the passengers as they arrive, and

their seat assignments, in the CheckIn table. One passenger doesn’t show, a new passenger

buys a ticket, and Jerry decides today is a good day to burn an upgrade coupon:

-- Merge Source table

CREATE TABLE

CheckIn

(

LastName VARCHAR(50),

FirstName VARCHAR(50),

FlightCode CHAR(6),

FlightDate DATE,

Seat CHAR(3)

);

INSERT

CheckIn

(LastName, FirstName, FlightCode, FlightDate, Seat)

VALUES ('LeBlanc, 'Patrick, 'SS2008', '20090301', '9F'),

('Jenkins', 'Sue', 'SS2008', '20090301', '7A'),

('Nixon', 'Jerry', 'SS2008', '20090301', '2A'),

('Anderson', 'Missy', 'SS2008', '20090301', '4B');

Before the MERGE command is executed, the next three queries look for differences in the

data. The fi rst set-difference query returns any no-show passengers. A LEFT OUTER JOIN

between the FlightPassengers and CheckIn tables fi nds every passenger with a reser-

vation joined with their CheckIn row if the row is available. If no CheckIn row is found,

then the LEFT OUTER JOIN fi lls in the CheckIn column with nulls. Filtering for the null

returns only those passengers who made a reservation but didn’t make the fl ight:

-- NoShows

SELECT F.FirstName + ' ' + F.LastName AS Passenger, F.Seat

FROM FlightPassengers AS F

LEFT OUTER JOIN

CheckIn AS C

ON C.LastName = F.LastName

AND C.FirstName = F.FirstName

AND C.FlightCode = F.FlightCode

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

http://www.it-ebooks.info