Sc7Ric1W

1
2IF OBJECT_ID(N'dbo.testTarget') IS NOT NULL DROP TABLE [dbo].[testTarget];
3IF OBJECT_ID(N'dbo.testSource') IS NOT NULL DROP TABLE [dbo].[testSource];
4IF OBJECT_ID(N'dbo.testRekey') IS NOT NULL DROP TABLE [dbo].[testRekey];
5
6
7create table dbo.testTarget(EID int, EmpName varchar(20), CONSTRAINT Target_PK PRIMARY KEY (EID));
8create table dbo.testSource(EID int, EmpName varchar(20), CONSTRAINT Source_PK PRIMARY KEY (EID));
9create table dbo.testRekey(mergeAction varchar(20), newID int, newName varchar(20), oldID int, oldName varchar (20));
10
11insert into [StrapsSource].[dbo].[testTarget] (EID, EmpName) VALUES (100,'Karen'),(101,'Jim'),(102,'Herb');
12insert into [StrapsSource].[dbo].[testSource] (EID, EmpName) VALUES (102,'Key Change!'),(103,'Susan'),(104,'Kyle');
13
14select * from [dbo].[testTarget];
15select * from [dbo].[testRekey];
16select * from [dbo].[testSource];
17
18MERGE [StrapsSource].[dbo].[testTarget] AS T -- TARGET
19USING [StrapsSource].[dbo].[testSource] AS S -- SOURCE
20	ON T.EID = S.EID
21
22WHEN MATCHED THEN							-- EXISTS IN TARGET, UPDATE
23	UPDATE SET T.EmpName = S.EmpName
24
25WHEN NOT MATCHED THEN						-- NOT IN TARGET, INSERT 
26	INSERT VALUES (S.EID, S.EmpName)
27
28OUTPUT $action AS 'action'					-- PUSH RESULTS INTO TABLE
29	, INSERTED.EID AS 'NewEID'
30	, INSERTED.EmpName AS 'NewEmpName'
31	, DELETED.EID AS 'OldEID'
32	, DELETED.EmpName AS 'OldEmpName'
33
34INTO [dbo].[testRekey];
35
36
37select * from [dbo].[testTarget];
38select * from [dbo].[testRekey];
39select * from [dbo].[testSource];