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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
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')
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

T-SQL
1
2
3
4
5
6
7
8
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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.*;
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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.*;
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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.*;
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

T-SQL
1
2
3
4
5
6
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')
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

T-SQL
1
2
3
4
5
6
7
8
9
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.*;
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

T-SQL
1
SELECT * FROM TestMerge
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