SQL 2008 for Developers: MERGE

SQL 2008 for Developers: MERGE 30 Aug 2007 12:21

As indicated previously, I had a strong level of feedback from the TechEd session I ran on SQL2008 commenting on how useful it was to get some good understanding about the new features being introduced. I thought as a result we could follow through the new "features of interest to developers" as we move through the SQL 2008 CTP cycle.

One of the first features introduced with the SQL 2008 CTP series is going to be one of the most useful for developers. Thats the introduction of a new statement called MERGE which allows us to elegantly manage two common database problems; UPSERT'ing (thats a conditional INSERT or UPDATE depending on the row being available) and synchronizing two tables.

Lets have a look at how it works.

The basic syntax for MERGE is as follows:

MERGE <source>
USING
( SELECT <expression> FROM <target>) <alias>
    ON <intersection>
WHEN MATCHED
    THEN <UPDATE | DELETE>
WHEN TARGET NOT MATCHED [AND <conditions>]
    THEN <INSERT>
WHEN SOURCE NOT MATCHED [AND <conditions>]
    THEN <UPDATE | DELETE>
;

Effectively we are asking to take two sets, the source and the aliased target selection and then join them on an intersection. Rather than specifying the type of join (inner, left outer selecting non intersecting rows, right outer selecting non intersecting rows) we are going to operate on all sides of that join simultaneously and take appropriate action depending on a condition of what we find in those respective sets.

The three part conditional (WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED) allows us to select which part to operate on (INNER, RIGHT OUTER and LEFT OUTER respectively) and then allows appropriate DML given the context. Our DML is always being performed on the <source> table, hence the filtered set of DML operations.

Here are a couple of examples of how this can be used..

Given two identical tables (for ease of example)

create table t1
(
Id int not null,
FullName varchar(100)
)

create table t2
(
Id int not null,
FullName varchar(100)
)

And some test data

insert into t1 values
( 1, 'Jeremy Boyd' ),
( 2, 'Adam Cogan' ),
( 3, 'Scott Hanselman' ),
( 4, 'John-Daniel Trask' )

insert into t2 values
( 1, 'Jeremy Boyd' ),
( 5, 'Andrew Peters' ),
( 6, 'John-Daniel Trask' )

We can syncronize these tables with this statement:

MERGE t1
USING
( select * from t2 ) target
ON t1.Id = target.Id
WHEN MATCHED
THEN UPDATE SET t1.FullName = target.FullName
WHEN TARGET NOT MATCHED
THEN INSERT VALUES( target.Id, target.FullName )
WHEN SOURCE NOT MATCHED
THEN DELETE;

The results, unsurprisingly look like t2! The statement itself updates 6 rows, 1 update on Id = 1 and 3, 2 inserts on Id's 5 and 6 and deletes on Id's 2,3,4.

Ok, lets have a look at the other type of scenario, a conditional INSERT/UPDATE

Again some fresh test data, this time we want the changes from t2 to be merged into t1

insert into t1 values
( 1, 'Jeremy Boyd' ),
( 5, 'Andrew Peters' ),
( 6, 'John-Daniel Trask' )

insert into t2 values
( 1, 'Jeremy Boyd' ),
( 5, 'Andrew "Ninja" Peters' ),
( 7, 'Darryl Burling' ),
( 8, 'Nigel Parker' )

So the MERGE statement will look very similar to the one above, but without the deletion of non existent rows from t2

MERGE t1
USING
( select * from t2 ) target
ON t1.Id = target.Id
WHEN MATCHED
THEN UPDATE SET t1.FullName = target.FullName
WHEN TARGET NOT MATCHED
THEN INSERT VALUES( target.Id, target.FullName );

Nice and easy!

One last thing to remember is that MERGE uses the closing semi-colon syntax to help the parser understand where your statement ends so don't forget that :)

comments (2)

jb's Blog

history

blogs i read

links