MERGE Statement (UPSERT) with Working Example

OK so this isn’t really a lesser known new feature in SQL Server 2008 but it’s a cool one so I figured I’d churn out a working example as those seem to be more popular than the “theory” posts.  So the idea behind MERGE is that you can take 2 sources and you can insert rows that do not exist and update rows that do exist.  Pretty cool stuff that used to require alot more typing and well by now you should know my problem with typing more than what’s absolutely necessary   So let’s get right to it.

CREATE TABLE tblNewMembers
(ID INT NOT NULL,
FirstName NVARCHAR(50),
LastName NVARCHAR(50))

INSERT INTO tblSQLServerPediaMembers
VALUES
(1, 'Clark', 'Kent'),
(2, 'Lana', 'Lang'),
(3, 'Lex', 'Luthor'),
(4, 'Lois', 'Lane')


OK and just for fun let’s have Lana Lang and Lois Lane both marry Clark Kent and take on the Kent last name. We’ll also add a new member Jimmy Olsen.

INSERT INTO tblSQLServerPediaMembers_UPSERTS
VALUES
(2, 'Lana', 'Kent'),
(4, 'Lois', 'Kent'),
(5, 'Jimmy', 'Olsen')

OK so let’s take our “UPSERTS” and MERGE them into the master table (tblSQLServerPediaMembers)

MERGE tblSQLServerPediaMembers A
using (SELECT * FROM [tblSQLServerPediaMembers_UPSERTS]) AS B
ON A.ID = B.ID
WHEN MATCHED THEN
   UPDATE SET
   A.FirstName = B.FirstName,
   A.LastName = B.LastName
WHEN NOT MATCHED
THEN INSERT VALUES (B.ID, B.FirstName, B.LastName);

Results: (3 row(s) affected)

So let’s take a look at our master table and see what we get:

SELECT *
FROM tblSQLServerPediaMembers


1 Clark Kent
2 Lana Kent
3 Lex Luthor
4 Lois Kent
5 Jimmy Olsen
 
 


Results look good as Jimmy Olsen is in the table now and Clark Kent is one happy guy.

For all the inner workings of the MERGE statement you can visit the 2008 BOL

Enjoy!!