MERGE statement in SQL Server

In the current releases of Oracle and MS SQL server ,we can use MERGE to ease the ETL process .

The implementation of MERGE statement in SQL Server 2008 seems little more advanced than Oracle's implementation.

Here is our use case.

a) If the record in the source table does not exist in the target table  , then add the record.
b) If the record in the source table matches with the record in the target table and there is another condition ( in our example , col3 in target table is 'N' ) ,then update the target record.
c) If the record in the target table does not exists in the source table , then delete it from the target table.

In Oracle's implementation of MERGE , the first two conditions can be easily implemented.
Here's where SQL Server's implementation of MERGE comes handy for the third condition .

Let us create tables ( t_source - for source table and t_dest - for destination table ) .


1> CREATE TABLE t_source
2>   (
3>     col1 INTEGER NOT NULL PRIMARY KEY ,
4>     col2 CHAR(30) NOT NULL ,
5>     col3 CHAR(1) NOT NULL
6>   ) ;
7> go

1>  CREATE TABLE t_dest
2>   (
3>     col1 INTEGER NOT NULL PRIMARY KEY ,
4>     col2 CHAR(30) NULL ,
5>     col3 CHAR(1) NULL
6>   ) ;
7> go



1> insert into t_source values ( 1 , 'Zahir Mohideen' , 'Y') ;
2> insert into t_source values ( 2 , 'Zahir Farook' , 'N') ;
3> insert into t_source values ( 3 , 'Mohideen Farook' , 'N') ;
4> go
(1 rows affected)



1> insert into t_dest values ( 2 , 'Zahir Farook' , 'N') ;
2> insert into t_dest values ( 3 , 'Mohideen Z Farook' , 'Y') ;
3> insert into t_dest values ( 4 , 'Kilakarai' , 'N') ;
4> go
(1 rows affected)


Here are the contents of the tables.

1> select * from t_source
2> go

col1 col2 col3
---- ---- ----
1 Zahir Mohideen Y
2 Zahir Farook N
3 Mohideen Farook N

(3 rows affected)

1> select * from t_dest;
2> go
col1 col2 col3
---- ---- ----
2 Zahir Farook N
3 Mohideen Z Farook Y
4 Kilakarai N

(3 rows affected)

Here is our implentaion of MERGE statement .

1> Merge t_dest d USING
2> ( SELECT col1 scol1 , col2 scol2 , col3 scol3 FROM t_source
3> ) s ON ( s.scol1 = d.col1 )
4> WHEN NOT matched THEN
5>   INSERT
6>     (
7>       col1 ,
8>       col2 ,
9>       col3
10>     )
11>     VALUES
12>     (
13>       s.scol1 ,
14>       s.scol2 ,
15>       s.scol3
16>     )
17> WHEN matched  AND col3 = 'N' THEN
18>   UPDATE SET col2 = s.scol2 , col3 = s.scol3
19> WHEN NOT matched BY source THEN
20>   DELETE ;
21> go

(3 rows affected)


The code in line # 4 is related to first condition
The code in line # 17 is related to second condition
The code in line # 19 is related to third condition


Here is the result set after running MERGE statement.

1> select * from t_dest
2> go

col1 col2 col3
---- ---- ----
1 Zahir Mohideen Y
2 Zahir Farook N
3 Mohideen Z Farook Y

(3 rows affected)

Comments Welcome.