Rotating Header Image

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))

CREATE TABLE tblSQLServerPediaMembers
(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!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

2 Comments

  1. Leon says:

    Hi,

    Thanks for the great example. I’m wondering.

    Is there a way to use a CASE WHEN scenario when using the UPSERT statement. I need this because it occurs that the source field contains NULL values and the destination field has NOT NULL values. The routine should then not update the destination field.

    Is it possible to create this scenario with UPSERT?

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.