Merge in SQL 2008

Many a times we came across situation to merge records between two tables. It will be like incremental update i.e., add new records and update existing records based on a reference column.

We usually accomplish this in two statements.

1. A join statement to update records.
2. A insert statement for new records.

Lets execute and see how this works.

Step 1: We have 2 tables one "EmployeeBulk1" and "EmployeeBulk2" tables, each with 20,000 records. First table having records with EmployeeId from 1 to 20000, second table with EmployeeId from 10,001 to 30,000. so, 10,000 records in common.

Step 2: Before execution of query, lets enable SQL Profiler to capture results.

Step 3: Prepare the query in traditional way as described above.

--Join part to update existing records
update tab1 set tab1.employeename=tab2.employeename,
    tab1.employeedepartment=tab2.employeedepartment,
    tab1.Company=tab2.company
from EmployeeBulk1 tab1 inner join EmployeeBulk2 tab2
on tab1.EmployeeId=tab2.employeeid
--Inser part for New records.
insert into EmployeeBulk1
select * from EmployeeBulk2 where employeeid not in
(select employeeid from EmployeeBulk1)

Step 4: Have a look at SQL profiler.

updating 10,000 records took 2929 ms and Inserting 10,000 new records took 566 ms. Total it took 3495 ms for this operation of merging.

Step 5: Lets have a look at the new "Merge" keyword introduced in SQL 2008, using which we do the same operation in one single statement.

--Using MERGE keyword from SQL 2008
Merge into  EmployeeBulk1 as tab1
using(select * from EmployeeBulk2) as tab2
on tab1.employeeid=tab2.employeeid
when matched then 
update set tab1.employeename=tab2.employeename,
    tab1.employeedepartment=tab2.employeedepartment,
    tab1.Company=tab2.company
when not matched then
insert values(tab2.employeeid,tab2.employeename,tab2.employeedepartment,tab2.company);

Step 7: Have a look at profiler now.

From the result you can see that the whole operation was completed in 853 ms. Its not only fast but also easy to handle Updates, Inserts and even Deletions in one single statement using "MERGE"

Is it helpful for you? Kindly let me know your comments / Questions.