In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.
Today we are going to take a look at how to do an upsert by using the MERGE statement. If you do not know what an upsert is, it basically will update rows in the table if there are already rows that match your condition, otherwise it will insert into the table
First let’s take a look at how you would do this before SQL Server 2008
Create this simple table
CREATE TABLE TestMerge (id int not null, SomeName varchar(100) not null)
go
INSERT TestMerge values(1,'Denis1')
INSERT TestMerge values(2,'Denis2')
INSERT TestMerge values(3,'Denis3')
INSERT TestMerge values(4,'Denis4')
INSERT TestMerge values(5,'Denis5')
INSERT TestMerge values(6,'Denis6')
INSERT TestMerge values(7,'Denis7')
One way to do an upsert in the pre 2008 days is to do the following
UPDATE TestMerge
SET SomeName = 'Denis10'
WHERE id = 10
IF @@ROWCOUNT = 0
INSERT INTO TestMerge VALUES (10,'Denis10')
SELECT * FROM TestMerge
DELETE TestMerge WHERE ID =10
As you can see, we do an update, if @@ROWCOUNT is 0, there were no rows update, this means we have to do an insert
Here is another way
IF EXISTS (SELECT 1 FROM TestMerge
WHERE ID = 10)
BEGIN
UPDATE TestMerge
SET SomeName = 'Denis11'
WHERE id = 10
END
ELSE
BEGIN
INSERT INTO TestMerge VALUES (10,'Denis10')
END
DELETE TestMerge WHERE ID =10
As you can see we check if we have a row with the id that we are looking for, if the exists check returns true, we update, otherwise we are doing an insert
Merge
Now here is how it is done in SQL Server 2008 and up
Be aware that when you use the MERGE statement, you need to terminate it with a semi-colon, otherwise you’ll get the following message
Msg 10713, Level 15, State 1, Line 10
A MERGE statement must be terminated by a semi-colon (;).
Here is an example to look at, I added comments so that you can see what is going on
DECLARE @ID int = 9, @Name varchar(100) = 'BLA10'
MERGE TestMerge AS target -- this is the table you are upadting/insert into
USING (SELECT @ID, @Name) AS source (id, SomeName) -- variables mapped to the columns
ON (target.id = source.id) -- this is the key column we want to join on
WHEN MATCHED THEN -- if we have a match then we update
UPDATE SET SomeName = source.SomeName -- update the SomeName column
WHEN NOT MATCHED THEN -- if we don't match then we do an insert
INSERT (id, SomeName) -- do an insert
VALUES (source.id, source.SomeName); --these values are the ones in the
-- variables mapped to the column
Let’s run an example, this will add a row with the id 9 and the value in the SomeName column will be BLA
DECLARE @ID int = 9, @Name varchar(100) = 'BLA9'
MERGE TestMerge AS target
USING (SELECT @ID, @Name) AS source (id, SomeName)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET SomeName = source.SomeName
WHEN NOT MATCHED THEN
INSERT (id, SomeName)
VALUES (source.id, source.SomeName);
SELECT * FROM TestMerge
Output -------------- 1 Denis1 2 Denis2 3 Denis3 4 Denis4 5 Denis5 6 Denis6 7 Denis7 9 BLA9
As you can see the row got added.
Now let’s try to use the same ID but we will change the value from BLA9 to BLA10
DECLARE @ID int = 9, @Name varchar(100) = 'BLA10'
MERGE TestMerge AS target
USING (SELECT @ID, @Name) AS source (id, SomeName)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET SomeName = source.SomeName
WHEN NOT MATCHED THEN
INSERT (id, SomeName)
VALUES (source.id, source.SomeName);
SELECT * FROM TestMerge
Output -------------- 1 Denis1 2 Denis2 3 Denis3 4 Denis4 5 Denis5 6 Denis6 7 Denis7 9 BLA10
And as you can see for the row with ID 9, the SomeName column was updated.
You can actually output from the MERGE statement directly what action it is taking and what the value before and after is, very similar with the inserted and deleted pseudo tables in triggers. All we have to do is add
OUTPUT deleted.*, $action, inserted.*
Deleted.* will have the values before the update and inserted will have the value what it was updated to, $action will have the value UPDATE, INSERT or DELETE
DECLARE @ID int, @Name varchar(100)
SELECT @ID = 9,@Name = 'BLA9'
MERGE TestMerge AS target
USING (SELECT @ID, @Name) AS source (id, SomeName)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET SomeName = source.SomeName
WHEN NOT MATCHED THEN
INSERT (id, SomeName)
VALUES (source.id, source.SomeName)
OUTPUT deleted.*, $action, inserted.*;
Output id SomeName $action id SomeName 9 BLA10 UPDATE 9 BLA9
Let’s run the same exact statement again
DECLARE @ID int, @Name varchar(100)
SELECT @ID = 9,@Name = 'BLA9'
MERGE TestMerge AS target
USING (SELECT @ID, @Name) AS source (id, SomeName)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET SomeName = source.SomeName
WHEN NOT MATCHED THEN
INSERT (id, SomeName)
VALUES (source.id, source.SomeName)
OUTPUT deleted.*, $action, inserted.*;
Output id SomeName $action id SomeName 9 BLA9 UPDATE 9 BLA9
See what happened, an update was fired but nothing needed to change, what if we don’t want the update to fire in that case? Here is what we can do
Instead of
WHEN MATCHED THEN
Add a condition that will check if the SomeName is the same in both the source and the target
WHEN MATCHED AND target.SomeName <> source.SomeName THEN
Now when you run this, you won’t get any output
DECLARE @ID int, @Name varchar(100)
SELECT @ID = 9,@Name = 'BLA9'
MERGE TestMerge AS target
USING (SELECT @ID, @Name) AS source (id, SomeName)
ON (target.id = source.id)
WHEN MATCHED AND target.SomeName <> source.SomeName THEN
UPDATE SET SomeName = source.SomeName
WHEN NOT MATCHED THEN
INSERT (id, SomeName)
VALUES (source.id, source.SomeName)
OUTPUT deleted.*, $action, inserted.*;
Besides variable, you can also use another table as a source
First create this table
CREATE TABLE TestMerge2 (id int not null, SomeName varchar(100) not null)
go
INSERT TestMerge2 values(1,'Denis1 from table existing')
INSERT TestMerge2 values(12,'Denis12 from table new')
Now we are going to change this line
USING (SELECT @ID, @Name) AS source
Change it to the following
USING (SELECT id, SomeName from TestMerge2) AS source
That will tell the merge statement to use the TestMerge2 table as the source
Now run the following
MERGE TestMerge AS target
USING (SELECT id, SomeName from TestMerge2) AS source (id, SomeName)
ON (target.id = source.id)
WHEN MATCHED AND target.SomeName <> source.SomeName THEN
UPDATE SET SomeName = source.SomeName
WHEN NOT MATCHED THEN
INSERT (id, SomeName)
VALUES (source.id, source.SomeName)
OUTPUT deleted.*, $action, inserted.*;
Output
id SomeName $action id SomeName NULL NULL INSERT 12 Denis12 from table new 1 Denis1 UPDATE 1 Denis1 from table existing
Now, let’s see what is in the table
SELECT * FROM TestMerge
id SomeName 1 Denis1 from table existing 2 Denis2 3 Denis3 4 Denis4 5 Denis5 6 Denis6 7 Denis7 9 BLA9 12 Denis12 from table new
As you can see the table contains what we expected
Hopefully this post gave you some ideas on how to use the MERGE statement to do upserts. Come back tomorrow for the next post in this series
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
