Sql server Merge Statement
SQL Merge is introduced in SQL server 2008. We can avail the feature of Merge from SQL 2008 versions. It behaves very similar to Upsert command (Update/Insert) of Oracle. It will automatically insert the records into a table if the record doesn't exist or will update if it exists.
The MERGE statement which merges the data or source table to a target table based on the condition that we specify and if the source data already exists in the target table or not. The SQL command Merge actually combines the basic DML commands INSERT, UPDATE and DELETE in a single atomic statement based on condition, depends on the record existence. The syntax of for using MERGE SQL command in SQL server as follows (Syntax from MSDN)
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH (
USING
ON
[ WHEN MATCHED [ AND
THEN
[ WHEN NOT MATCHED [ BY TARGET ] [ AND
THEN
[ WHEN NOT MATCHED BY SOURCE [ AND
THEN
[
[ OPTION (
;
When we write a Merge statement, we need to specify source and target tables and the join clause for linking those tables. We also need to specify the type of modification need to be occurred when records between the tables are matched or not matched. We should specify the appropriate WHEN Clause when we build MERGE statgement. There are three types WHEN clauses we can apply for Merge statement and those are
1.WHEN MATCHED
2. WHEN NOT MATCHED [BY TARGET] Clause
3. WHEN NOT MATCHED BY SOURCE Clause
Let's consider the following example , We have two tables on Employee records which are EmployeeOld and EmployeeNew, The old table was not used by most of the systems. However only one application is using this table. Hence we need to update the old with new table records using Merge.
EmployeeOld Table
EmpID EmpName Designation Salary Active
101 AAA Manager 1000 A
102 BBB Lead 500 A
103 CCC Manager 1200 A
104 DDD Tester 700 D
Employee New Table
EmpID EmpName Designation Salary Active
101 AAA Manager 1200 A
102 BBB Lead 600 A
103 CCC Manager 1200 A
105 EEE Software Engineer 600 A
We need to apply the following conditions when we Merging the tables
we will consider three main conditions while we merge this two tables.
1. Delete the records Which are not availbale in Newtbale
2. Update the exisitng source table records with target table values based on EmployeeID
3. Insert new records if record does not exists.
MERGE EmpOldTable AS TARGET
USING EmpTable AS SOURCE
ON (TARGET.EmpID = SOURCE.EmpID)
--If the record exists in target table then update those records
WHEN MATCHED AND TARGET.EmpName <> SOURCE.EmpName OR TARGET.Designation <> SOURCE.Designation
OR TARGET.Salary <> SOURCE.Salary THEN
UPDATE SET TARGET.EmpName = SOURCE.EmpName,
TARGET.Designation = SOURCE.Designation,
TARGET.Salary = SOURCE.Salary
--if the record doesn't exist, Insert the source records into target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (EmpID, EmpName, Designation,Salary)
VALUES (SOURCE.EmpID, SOURCE.EmpName,SOURCE.Designation, SOURCE.Salary)
--If the target table record doesn't exist on source table then delete that records from target table.In our case, "DDD " will be deleted.
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
GO
Things to be taken care
1. Merge statement should be ended with semicolon(;)
2. If there is a MATCH clause in the Merge condition, it should be specified as the first condition amongst all other WHEN clauses.
Now check for the new resultset to verify all the three conditions are carried out.
Output:
EmpID EmpName Designation Salary Active
101 AAA Manager 1200 A
102 BBB Lead 600 A
103 CCC Manager 1200 A
105 EEE Software Engineer 600 A
The Ships's Voyages I feel technological know-how just causes it to be even worse. Now there is a channel to never ever care, now there wouldn't be considered a likelihood for them to find .