Comparing values of Two Table and Performing Insert and Update using Merge in SQL

Comparing values of Two Table and Performing Insert and Update using Merge in SQL 

Issue-Matching the values of Two tables if matched then Update a value else Insert in source table

Issue Description-Inserting XML data into a temp table comparing the temp table Records with the main table records based on some conditions
If Records matched then update  main table values with temp table
IF records didn't matches then Insert into Main table

Issue Solution-Will show the Result by creating two tables one main table and second test table

--Main table Script
CREATE TABLE [dbo].[[MyEmploys]](
[EmployeeID] [smallint] NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](40) NULL,
[Title] [nvarchar](50) NULL,
[DeptID] [smallint] NULL,
[ManagerID] [int] NULL,
 CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED 
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (1, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (11, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (22, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (112, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (200, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (201, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)

GO


--Test Table Variable which is storing xml data to test i am inserting some values in test table

declare @employeetable table
( id int,
 name nvarchar(100),
 Title nvarchar(100)
 )
-- the first Row which we are inserting is already present there so it will update the Main table employee id with test table id other two record it will insert
 insert into @employeetable values(250,'Syed','Pacific Sales Manager'),(500,'Cat',' Officer'),(150,'Ken','Chief ')

 --Here is the Final script

 MERGE [MyEmploys] AS stm
USING (SELECT * from @employeetable) AS sd
--checking conditon where name and title matches update main table EmployeeiD
ON stm.firstname=sd.name and stm.title=sd.title
WHEN MATCHED THEN  update set stm.employeeid=sd.id 
 --when name and title are not matching inserting the record
WHEN NOT MATCHED THEN
insert (employeeid,firstname,title) 
values(sd.id,sd.name,sd.title);

--verify your result
Select * from [MyEmploys]

EmployeeID FirstName LastName Title DeptID ManagerID
1 Ken NULL Chief Executive Officer NULL NULL
11 Ken NULL Chief Executive Officer NULL NULL
16 David Bradley Marketing Manager 4 273
22 Ken Sánchez Chief Executive Officer 16 NULL
23 Mary Gibson Marketing Specialist 4 16
112 Ken NULL Chief Executive Officer NULL NULL
150 Ken NULL Chief NULL NULL
200 Ken NULL Chief Executive Officer NULL NULL
201 Ken NULL Chief Executive Officer NULL NULL
250 Syed Abbas Pacific Sales Manager 3 273
273 Brian Welcker Vice President of Sales 3 1
274 Stephen Jiang North American Sales Manager 3 273
275 Michael Blythe Sales Representative 3 274
276 Linda Mitchell Sales Representative 3 274
286 Lynn Tsoflias Sales Representative 3 285
500 Cat NULL Officer NULL NULL