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.
Step 4: Have a look at SQL profiler.
--Join part to update existing recordsupdate 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 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.
Step 6: Results are
--Using MERGE keyword from SQL 2008 Mergeinto 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.
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.