Merge Statement in SQL Server

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.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
THEN <merge_ matched> ];

e.g.-
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);