What do you do when your application logic requires that you
INSERT a record if the record doesn’t exist, or UPDATE the record if it does
exist? If you are using SQL Server 2005 or below you would have to write a series
of T-SQL statements that check to see if the record exists, and then write an
UPDATE or INSERT statement depending on your existence checks. With SQL Server
2008, Microsoft has simplified the amount of T-SQL code it requires to perform
INSERT/UPDATE logic, by implementing the MERGE statement. This article will
explore how to use the MERGE statement.
What can you do with the MERGE Statement
You can do more with the MERGE statement then just inserting
and updating records as described above. You can also use it to delete
records. Another thing you can do with the MERGE statement is to perform a
specific number of merge actions based on a TOP clause. The MERGE statement
can also generate output that will identify which records where inserted,
updated, or deleted. The best way to describe how the new MERGE statement works
is to go through some examples, and examine how they work.
Basic INSERT or UPDATE Example
For the first example, I will perform updates or inserts to
a target table based on a source table. The source table will contain the
records you either want to insert or update, and the target table will be the
table where the inserts or updates will actually be performed. This kind of
operation is sometimes referred to as an “upsert”.
For this example I will be performing inserts and updates to
a “Customers” table. My “Customer” table will be considered the target table.
The records I want to insert or update will be contained in a table named
“NewCustomers”. This table is known as the source table.
Here is the code to create and populate the target and
source tables:
SET NOCOUNT ON;
— Create Target Table
CREATE TABLE Customers (LastName VARCHAR(50), FirstName VARCHAR(50));
INSERT INTO Customers VALUES (‘Doe’, ‘Jane’);
— Create Source Table
CREATE TABLE NewCustomers(LastName VARCHAR(50), FirstName VARCHAR(50));
INSERT INTO NewCustomers VALUES (‘Doe’, ‘John’);
INSERT INTO NewCustomers VALUES (‘Smith’, ‘Doris’);
I’ve created a Customers table with a single record, and a
NewCustomers table with two different records. The records in the NewCustomers
table will be used to update and insert records in to the Customers table. The
first record in the NewCustomers table is going to be used to update the single
existing Customers record, where as the second record in the NewCustomers table
is going to be inserted into the Customers table. To do the insert and update,
I will use the following MERGE statement:
— Update and Insert into Customers
MERGE Customers AS C
USING NewCustomers AS NC
ON C.LastName = NC. LastName
WHEN MATCHED THEN
UPDATE SET C.FirstName = NC.FirstName
WHEN NOT MATCHED THEN
INSERT (LastName, FirstName) VALUES (NC.LastName,NC.FirstName);
Here the target table is identified by naming it immediately
following MERGE statement, in my example that would be “Customers”. Next, the
MERGE statement uses the “USING” clause to identify the source of the updates
and inserts, which in this case is the NewCustomers table. In order to
identify which records are updates and which ones are inserts I need to
identify a key column to match on between the Customers and NewCustomers
table. To accomplish this matching of records between the two tables the MERGE
statement uses the “ON” clause just as you would do when you are joining two
tables. Here I am joining the source and target tables based on the LastName
column in source and target tables. The different “WHEN” criteria of the MERGE
statement identify the conditions for when an update or insert is to be
performed. When a record is found in both source (NewCustomers) and target
(Customers) tables then an UPDATE statement is performed to update on the FirstName
column. This condition meets the “WHEN MATCHED” criteria above. When a join does
not find a record in the target (Customers) table for a record in the source (newCustomer),
then this is the “WHEN NOT MATCHED” criteria is executed. When this occurs an
INSERT statement is performed to insert the LastName and FirstName value into
the Customers table. After I have run my MERGE statement, my updated
Customers table contains the following records:
LastName FirstName
—————————————— ————————————————–
Doe John
Smith Doris
By looking at the output, you can see the MERGE statement
changed the FirstName column of the “Doe” record from “Jane” to “John”. The
MERGE did an UPDATE to accomplish this by using the “MATCHED” criteria. The
second record was created when the “NOT MATCH” criteria inserted a record.
Basic Insert, Update and Delete Example using MERGE
The MERGE statement can be
used to maintain a target table based on information in a source table. By
maintaining a target table, I mean the MERGE statement can perform the
following actions:
-
Insert a new record from the
source if the record is not in the target. -
Update a target record if the
record exists in the source table. -
Delete a record in the target
table if the record is not found in the source.
To
show how this works let me go through an example. My example will use the MERGE
statement to maintain a student roster for the students enrolled in the current
year. For this example, my target table will be “Students”. Here is the code
to create and populate my “Students” table:
CREATE TABLE Students (LastName VARCHAR(50),
FirstName VARCHAR(50),
Address VARCHAR(100),
Age INT);
INSERT INTO Students
VALUES (‘Fritz’, ‘David’, ‘181 Kline Street’, 14)
,(‘Reese’, ‘Paul’ , ‘4429 South Union’, 14)
,(‘Brown’, ‘Jake’ , ‘5401 Washington Ave’,14);
My student table needs to be
updated every year with a list of all the new students as well as the returning
students. To do this I need a source table that will contain those records
that need to be inserted, or updated. Here is the code to create and populate
my source table:
CREATE TABLE NewYearRoster(LastName VARCHAR(50),
FirstName VARCHAR(50),
Address VARCHAR(100),
Age INT);
INSERT INTO NewYearRoster
VALUES (‘Fritz’, ‘David’, ‘181 Kline Street’, 15)
,(‘Reese’, ‘Paul’, ‘1950 Grandview Place’, 15)
,(‘Adam’, ‘Wilbur’, ‘4231 W. 93rd’, 15);
My merge example will be
using the source table (NewYearRoster) to maintain the target table (Students)
by inserting new students, updating students information if they were enrolled
last year (meaning already in Student table), and delete student records if
they are not enrolled for the new year (meaning not in NewYearRoster table).
To accomplish this here is my MERGE statement:
MERGE Students AS T
USING NewYearRoster AS S
ON S.LastName = T. LastName and
S.FirstName = T.FirstName
WHEN MATCHED THEN
UPDATE SET T.Address = S.Address,
T.Age = S.Age
WHEN NOT MATCHED THEN
INSERT (LastName,
FirstName,
Address,
Age)
VALUES (S.LastName,
S.FirstName,
S.Address,
S.Age)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
After I run the above command,
my Student table contains the following records:
LastName FirstName Address Age
————– —————- ———————————————— ———–
Fritz David 181 Kline Street 15
Reese Paul 1950 Grandview Place 15
Adam Wilbur 4231 W. 93rd 15
If you review this with my
original Students table, you will find that the above MERGE statement did the
following:
-
David Fritz’s record got the Age
columns updated (also the address was updated but it was the same in both the
source and target tables) -
Paul Reese’s record got the
Address and Age columns updated -
Jake Brown’s student record was
deleted - Adam Wilbur’s record was inserted
Adding Additional MERGE Search Conditions
You can add additional
conditions to the MATCH or NOT MATCH clauses to make your MERGE search
conditions more complicated. Below I have modified my above simple insert,
update, or delete example to only update existing records if the address in the
NewYearRoster is different from the current Students address record:
MERGE Students AS T
USING NewYearRoster AS S
ON S.LastName = T. LastName and
S.FirstName = T.FirstName
WHEN MATCHED and T.Address <> S.Address THEN
UPDATE SET T.Address = S.Address,
T.Age = S.Age
WHEN NOT MATCHED THEN
INSERT (LastName,
FirstName,
Address,
Age)
VALUES (S.LastName,
S.FirstName,
S.Address,
S.Age)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
I added my additional MERGE
search condition to the “WHEN MATCHED” clause, so now it reads “WHEN MATCHED
and T.Address <> S.Address THEN UPDATE…”. By doing this the David
Fritz‘s Students record does not get updated because this record has the same
address in both the target and source tables.
Output Clause
If you want to find out what
records are being inserted, updated, or deleted when you run a MERGE statement
you can use the OUTPUT clause. This clause will output information you specify
for every record inserted, updated, and/or deleted. I’ve modified my basic
insert, update, and delete example above to output the type of action taken,
plus the First and Last name. Here is that modified MERGE statement.
MERGE Students AS T
USING NewYearRoster AS S
ON S.LastName = T. LastName and
S.FirstName = T.FirstName
WHEN MATCHED THEN
UPDATE SET T.Address = S.Address,
T.Age = S.Age
WHEN NOT MATCHED THEN
INSERT (LastName,
FirstName,
Address,
Age)
VALUES (S.LastName,
S.FirstName,
S.Address,
S.Age)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action, Inserted.LastName, Inserted.FirstName, Deleted.LastName, Deleted.FirstName;
When I run this statement, I
get the following output:
$action LastName FirstName LastName FirstName
———- ———— ————- ————- ——————-
INSERT Adam Wilbur NULL NULL
UPDATE Fritz David Fritz David
UPDATE Reese Paul Reese Paul
DELETE NULL NULL Brown Jake
Now you can tell this
statement performed 4 different actions, 1 insert, 2 updates and 1 delete
statement.
Using Top Clause in MERGE Statement
You can add a TOP clause to
the MERGE statement. When you do this, the MERGE statement will limit the
number of actions performed to the number specified in the TOP clause. Below I
have modified my basic insert, update, and delete example to contain a TOP
clause to request that only 2 actions be taken:
MERGE TOP (2) Students AS T
USING NewYearRoster AS S
ON S.LastName = T. LastName and
S.FirstName = T.FirstNameWHEN NOT MATCHED THEN
INSERT (LastName,
FirstName,
Address,
Age)
VALUES (S.LastName,
S.FirstName,
S.Address,
S.Age)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
WHEN MATCHED THEN
UPDATE SET T.Address = S.Address,
T.Age = S.Age
OUTPUT $action, Inserted.LastName, Inserted.FirstName, Deleted.LastName, Deleted.FirstName;
Here is the output produced
by the OUTPUT clause when I run this MERGE statement:
$action LastName FirstName LastName FirstName
———- ———— ————- ————- ——————-
UPDATE Fritz David Fritz David
UPDATE Reese Paul Reese Paul
Here the MERGE statement
only performed 2 UPDATE statements. If you look at my MERGE statement I
arranged the WHEN clause to have the UPDATE statement as the last set of
actions to be taken. However, you can see that my MERGE statement did not
perform the actions in the order they where specified in the MERGE statement.
If you play with this statement you will find the updates, are performed first,
then inserts, and lastly delete statements will be performed.
Benefits of Using MERGE
The MERGE statement
simplifies the amount of work it takes to implement insert and update logic.
Now with a single statement you can easily implement insert, update, and delete
logic to handle criteria for maintaining a table. The MERGE statement handles
all the joining of the source and target. This minimizes the amount of code
you need to write to handle merge logic. The MERGE statement is just one of
the many enhancements that Microsoft has included in SQL Server 2008.