Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Merge in SQL 2008

, 23 Jul 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Many a times we came across situation to merge records between two tables.

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 6: Results are

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150414.1 | Last Updated 23 Jul 2012
Article Copyright 2012 by PratapReddyP
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid