MERGE Sample

CREATE TABLE [dbo].[source](
[id] [int] NULL,
[name] [nvarchar](1000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[source] ([id], [name]) VALUES (5, N'indika')
INSERT [dbo].[source] ([id], [name]) VALUES (6, N'kannangara')
INSERT [dbo].[source] ([id], [name]) VALUES (2, N'saminda')

CREATE TABLE [dbo].[target](
[id] [int] NULL,
[name] [nvarchar](1000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[target] ([id], [name]) VALUES (2, N'saminda')
INSERT [dbo].[target] ([id], [name]) VALUES (3, N'sunny')
INSERT [dbo].[target] ([id], [name]) VALUES (6, N'kanna')


SELECT * FROM [dbo].[source]

id name
5 indika
6 kannangara
2 saminda

SELECT * FROM [dbo].[target]

id name
2 saminda
3 sunny
6 kanna

MERGE INTO dbo.target T
USING (
SELECT [id]
      ,[name]
  FROM [test].[dbo].[source]) S
ON (S.[id]=T.[id] )
WHEN MATCHED
AND NOT S.[name] = T.[name]
THEN  UPDATE SET
[name] = S.[name]
WHEN NOT MATCHED THEN
  INSERT (
[id]
  ,[name])
  VALUES (
S.[id]
  ,S.[name])
WHEN NOT MATCHED BY SOURCE
THEN DELETE;


SELECT * FROM [dbo].[target]

id name
2 saminda
6 kannangara
5 indika