314
Part II: Building Databases and Working with Data
Merging Data
An upsert operation is a logical combination of an insert and an update. If the data isn’t
already in the table, the upsert inserts the data; if the data is already in the table, then
the upsert updates with the differences. Ignoring for a moment the MERGE command in SQL
Server, you can code an upsert operation with T-SQL in a few ways:■ (^) The most common method is to attempt to locate the data with an IF EXISTS; if
the row is found, UPDATE; otherwise INSERT.
■ (^) If the most common use case is that the row exists and the UPDATE is needed, then
the best method is to do the update; if @@RowCount = 0, then the row was new,
and the insert should be performed.
■ (^) If the overwhelming use case is that the row would be new to the database, then
TRY to INSERT the new row; if a unique index blocked the INSERT and fi red an
error, then CATCH the error and UPDATE instead.
All three methods are potentially obsolete with the new MERGE command. The MERGE com-
mand is well done by Microsoft — it solves a complex problem with a clean syntax and good
performance.
First, it’s called “merge” because it does more than an upsert. Upsert inserts or updates
only; merge can be directed to insert, update, and delete all in one command.
In a nutshell, MERGE sets up a join between the source table and the target table and can
then perform operations based on matches between the two tables.
To walk through a merge scenario, the following example sets up an airline fl ight check-in
scenario. The main work table is FlightPassengers, which holds data about reserva-
tions. It’s updated as travelers check in, and by the time the fl ight takes off, it has the
actual fi nal passenger list and seat assignments. In the sample scenario, four passengers
are scheduled to fl y SQL Server Airlines fl ight 2008 (Denver to Seattle) on March 1, 2008.
Poor Jerry, he has a middle seat on the last row of the plane — the row that doesn’t recline:
USE AdventureWorks;
-- Merge Target Table
CREATE TABLE
FlightPassengers
(
FlightID INT NOT NULL
IDENTITY
PRIMARY KEY,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
FlightCode CHAR(6) NOT NULL,
FlightDate DATE NOT NULL,
c12.indd 314c12.indd 314 7/30/2012 4:42:42 PM7/30/2012 4:42:42 PM
http://www.it-ebooks.info