Merging Data in SQL 2008

Merging Data in SQL 2008

SQL Server 2008 introduced a new feature Merge.
Using Merge statement you can perform DML insert update delete in a single query based on conditions.

Below is the demonstration of Merge statement with Two tables EmployeeMerge and EmployeeMerge1.

CREATE TABLE [dbo].[Employee_Merge](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Merge] ([Em
pid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 3000)


CREATE TABLE [dbo].[Employee_Merge1](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 15000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'amit', 18000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'vijay', 10000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'suresh', 5000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'Mukesh', 3000)

Select * from Employee_Merge

Empid EmpName EmpSalary
1 sumit 5000
3 amit 8000
5 vijay 10000
7 suresh 5000
9 Mukesh 3000

Select * from Employee_Merge1

Empid EmpName EmpSalary
1 sumit 15000
2 amit 18000
3 vijay 120000
4 suresh 5000
5 Mukesh 3000

--Merge Statement that will check condition and based on conditions it will perform DML Operations.

MERGE INTO [Employee_Merge] AS TargetTblT 
USING dbo.[Employee_Merge1] AS SourceTbl
 ON TargetTblT.empid = SourceTbl.empid 
 --First condition values of both tables matched and salary of Targertbl=10,000 then deleting that Record from Target table
WHEN MATCHED and TargetTblT.EmpSalary=10000 then delete
--Second Condition when Both empid matched then update the Target table with Source Table values
when Matched Then UPDATE SET 
 TargetTblT.[EmpName] = SourceTbl.[EmpName], 
 TargetTblT.[EmpSalary] = SourceTbl.[EmpSalary]
 --Third Condition where values of Source and Target Table doesn't matches Insert Record from source table to Target table
 WHEN NOT MATCHED 
 THEN INSERT ([Empid], [EmpName], [EmpSalary]) VALUES (SourceTbl.empid, SourceTbl.[EmpName], SourceTbl.[EmpSalary]);

Output 

Empid EmpName EmpSalary
1 sumit 15000
3 vijay 12000
7 suresh 5000
9 Mukesh 3000
2 amit 18000
4 suresh 5000

NOTE: If there is Matched condition with some other condition like in our code where salary =10,000 it should always be the first condition specified in  Matched like in our code and  never forget to add semi colon after termination of Match Statement.
other wise you will get the  below error.

Msg 10713, Level 15, State 1, Line 14

A MERGE statement must be terminated by a semi-colon (;).