The MERGE Statement
Published: May 26, 2014
So you’re working with some data and maybe you have another table which has newer, updated data... how do we combine these two tables to create one, correct, dataset?

This sounds simple enough but when you consider that the newer dataset may contain new records, require old records to be deleted, and also need certain records just to be updated. Suddenly this sounds more complicated than first anticipated.

We could use a combination of intersect, except, left and right joins, but much more simply, we could achieve this entire operation using just one MERGE statement.

To set the scene, I’ll be using this base data for the entire article…

if object_id('tempDB..#name') is not null drop table #name
if object_id('tempDB..#newName') is not null drop table #newName
go

create table #name
(
  
id int not null,
  
firstName varchar(10) not null,
  
lastName varchar(15) not null,
  
address1 varchar(100) not null,
  
town varchar(20) not null,
  
constraint pk_name primary key clustered(id)
)
create table #newName
(
  
id int not null,
  
firstName varchar(10) not null,
  
lastName varchar(15) not null,
  
address1 varchar(100) not null,
  
town varchar(20) not null,
  
constraint pk_newName primary key clustered(id)
)
go

insert into #name
  
values(1, 'Homer', 'Simpson', '123 SpringField Lane', 'New York'),
           (
2, 'Peter', 'Griffin', '31 Spooner Street', 'Quahog'),
           (
3, 'Eric', 'Cartman', '21208 E. Bonaza Cir.', 'South Park')
insert into #newName
  
values(1, 'Homer', 'Simpson', '742 Evergreen Terrace', 'Springfield'),
           (
2, 'Peter', 'Griffin', '31 Spooner Street', 'Quahog'),
           (
4, 'Sheldon', 'Cooper', 'Apt 4A, 2311 N. Los Robles Ave', 'Pasadena')
;
select * from #name
select * from #newName




Looking at the output it’s clear to see that the new data requires us to update 1 record (Homer’s address), to delete 1 record (Eric Cartman), to insert 1 new record (Sheldon Cooper), and to leave 1 record untouched (Peter Griffin).

So, prior to the MERGE statement, how would we achieve this? Well there are numerous options that could be chosen so I’ve included one such option below:

update n
set address1 = nn.address1,
  
town = nn.town
from #name n
join #newName nn
on n.id = nn.id

/*
This works because we have already updated all records so that they match,
otherwise I would not be using * and this would be more complicated
using a subquery in a where clause
*/
insert into #name
select *
from #newName
except
select
*
from #name

delete from #name
where id not in
(
  
select id
  
from #newName
)

select *
from #name


As you can see, this achieves the goal, but not without some fiddly and long winded SQL across multiple statements therefore increasing the chances of mistakes and errors creeping in.

Also if you look at the logical reads taken to complete this statement then you’ll see that there are quite a few across all the steps and even on such small tables they soon add up…



So how does the MERGE statement allow us to deal with this? Well it simply incorporates all the above actions into just one handy transaction.

All we’re doing is stating that we want data merging into our original table using the new table and joining them on the primary key that they have in common.

merge #name n
using
#newName nn
on
(
  
n.id = nn.id
)


So now let’s look at just the record update first…

As you would expect with an update we state that based on a matched key we are looking for any differences in the address and town columns:

merge #name n
using
#newName nn
on
(
  
n.id = nn.id
)
when matched and
(
  
n.address1 != nn.address1 or
  
n.town != nn.town
)


So, if we find any differences in these columns based on a primary key match what do we do? Well then we…

then
   update
   set
n.address1 = nn.address1,
      
n.town = nn.town


For the updates, it’s as simple as that.

Let’s insert our new record now. If we break it down into English, what we’re requesting is the following:

“If we don’t match on Primary Key (because the record is missing from the original table) then insert the new record.”

when not matched then
   insert
(id, firstName, lastName, address1, town)
  
values(nn.id, nn.firstName, nn.lastName, nn.address1, nn.town)


Likewise with the delete…

“When we don’t match because the record is not in our source table (new data table) then delete it”

when not matched by source then
   delete
;


It’s really that simple.

Putting it all together and running the entire statement we see that the task is completed as per our request:

merge #name n
using
#newName nn
on
(
  
n.id = nn.id
)
when matched and
(
  
n.address1 != nn.address1 or
  
n.town != nn.town
)
then
   update
   set
n.address1 = nn.address1,
      
n.town = nn.town
when not matched then
   insert
(id, firstName, lastName, address1, town)
  
values(nn.id, nn.firstName, nn.lastName, nn.address1, nn.town)
when not matched by source then
   delete
;

select *
from #name




And, remembering all the logical reads taken in our original coded effort, let’s have a look at the reads used by the MERGE statement:



As you can see it’s not a difficult command to get to grips with and it’s considerably more efficient than the previous alternatives.

Also you don’t have to include all sections in one statement. For example, if you only want to update and insert then you can leave out the whole delete statement. Likewise if you only want to insert and delete, then you can remove the update code. It’s incredibly flexible to suit all needs.

There is only one (quite large) gotcha with this command though and that is when using nullable columns, but there is a neat solution for this which I cover here.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron