Click here to Skip to main content
14,447,974 members

Merge in SQL Server 2008

Rate this:
4.59 (10 votes)
Please Sign up or sign in to vote.
4.59 (10 votes)
8 May 2013CPOL
Merge in SQL Server 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.

Image 1

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,
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.

Image 2

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,
when not matched then
insert values(tab2.employeeid,tab2.employeename,tab2.employeedepartment,;
Step 6: Results are

Image 3

Step 7: Have a look at profiler now.

Image 4

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.


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


About the Author

Comments and Discussions

QuestionHOw to update remote database with local database? Pin
Member 80572736-Jun-16 5:30
MemberMember 80572736-Jun-16 5:30 
QuestionA very good post for the beginners. Thank you Pin
Ranganath Prasad9-Oct-13 0:10
MemberRanganath Prasad9-Oct-13 0:10 
Questionreally good one Pin
Nidhiupadhyay31-Jul-13 8:10
MemberNidhiupadhyay31-Jul-13 8:10 
GeneralMy vote of 4 Pin
Mohd. Mukhtar12-Jun-13 0:15
MemberMohd. Mukhtar12-Jun-13 0:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 8 May 2013

Tagged as


11 bookmarked