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

One way to do an upsert in the pre 2008 days is to do the following

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

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

Let's run an example, this will add a row with the id 9 and the value in the SomeName column will be BLA

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

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

Output
id	SomeName    $action	id	SomeName
9	BLA10	    UPDATE	9	BLA9

Let's run the same exact statement again

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

Besides variable, you can also use another table as a source

First create this table

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

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

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