Merge in SQL 2008

As promised, here is the code examples I used to demonstrate the cool new Merge Keyword in SQL 2008 at the Sydney Business & Technology Usergroup.

So the Merge Keyword is new in SQL 2008. It allows you to build a T-Sql statement to compare data in 2 tables, and update, insert or delete accordingly based on whether or not the rows match. It is a neat replacement to the Upsert concept and allows you to cleanly encapsulate the logic.

The MSDN definition for the merge keyword can be found here: http://msdn.microsoft.com/en-us/library/bb510625(SQL.100).aspx

I like to break it down further into the following:

MERGE DESTINATION
USING (This Source Data) ON this join
When Matched (exists in both tables)
Do Something – probably Update
When Not Matched (A new record)
Do Something like Insert
When Source Not Matched (Row has been deleted from source table)
Do Something like Delete from Destination
OUTPUT;



Being a usergroup, I decided to use a Pizza theme for the examples... :)



I started with 2 very simple tables:
create table tblPizza1(Id int not null,Topping varchar(100))
create table tblPizza2(Id int not null,Topping varchar(100))


Then inserted some random data:
insert into tblPizza1
values
( 1, 'Hawaiian' ),
( 2, 'Vegetarian' ),
( 3, 'Meatlovers' ),
( 4, 'BBQ Chicken' )


insert into tblPizza2
values
( 1, 'Hawaiian' ),
( 5, 'Supreme' ),
( 6, 'Super Supreme' )


Now lets look at the Merge statement itself:

MERGE tblPizza2 destination
USING( select * from tblPizza1 ) source
ON destination.Id = source.Id
WHEN MATCHED
THEN UPDATE SET destination.Topping = source.Topping
WHEN TARGET NOT MATCHED
THEN INSERT VALUES( source.Id, source.Topping )
WHEN SOURCE NOT MATCHED
THEN DELETE
OUTPUT $ACTION, source.ID, Source.Topping;

So, the first line highlights that the destination table where the changes will be going to is going to be tblPizza2. We give it the alias of destination

Next, we define the source as (select * from tblPizza1) and give it the alias on source

And then we identify how we know that a row in one table is matched to a row in the other table in this example it is using the ID column in each table: ON destination.Id = source.Id

After this we break down what we do if there is a match. In this example we set the destination Topping to equal what ever the Topping is in the source database

If the data is in the source table, but it is not found in the target destination table, we insert the records from the Source table into the Destination table

if the data is in the destination table, but no longer in the source table, then it must have been deleted so we go on & delete the record from the destination table.

The last line provides the defintion of the output results, where $action refers to whether it is an Insert, Update or Delete

So once we have run the above statement, you will get an output table that lists the results. You can also check the tables directly to see what they show:

select * from tblPizza1
select * from tblPizza2

So to see more of what the statement can do, we make some changes to the database tables:
Update tblPizza1
Set Topping= 'Tropicana'
where ID=1

Insert into tblPizza1
values(5, 'Supreme')

Insert into tblPizza1
values(6, 'Super Supreme')

Delete from tblPizza1 where ID=4

Now we will run the Merge statement again, but this time its a little different, see the line in bold below:
MERGE tblPizza2 destination
USING( select * from tblPizza1 ) source
ON destination.Id = source.Id
WHEN MATCHED and destination.Topping <> source.Topping
THEN UPDATE
SET destination.Topping = source.Topping
WHEN TARGET NOT MATCHED
THEN INSERT VALUES( source.Id, source.Topping )
WHEN SOURCE NOT MATCHED
THEN DELETE
OUTPUT $ACTION, source.ID, Source.Topping;

Notice now that when the data is matched, we also check to see if the topping has changed. This is pretty handy when you have large tables for example in a datawarehouse scenario, and you dont want to update every row if it hasnt changed. We only have 2 columns, so only check if the topping value has changed - but you can put the T-SQL logic you need into this statement.

After running this statement, you will see that only the row with ID = 1 is updated & returned in the output results.

Slide Share

I have uploaded the Slides to slideshare

View Upload your own