Scenario (Updating the existing records and replacing with new entry and inserting new records)

Scenario: The details in the table is updated with IsActive, StartDate and EndDate columns to manage the History. The given script in this scenario is to reflect the below condition.


Condition: A table is already updated with records of product and representative details.
(1). When an existing product details updated in source, then the existing record should be udpated with EndDate  and '0' in IsActive column in destination. A new row should be inserted with the updated values with StartDate and '1' as IsActive. 
(2). When an existing employee details updated in source, then the existing record should be updated with respective changes in destination. No need to insert new row.
(3). When a new entry made in the source, then that should be inserted in the destination table.
(4). Delete the record in Destination when the record is deleted in source.


Below are the source tables:
ExamleTable1: work.dbo.Product

ExampleTable2: work.dbo.Employee

Now the below SQL tasks are used to perform the scripts for conditions.

Script to delete records in Destination those are deleted in source.

MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS  HashKey
  FROM dbo.Employee S1
    JOIN dbo.Product S2
       ON s1.EmpId = s2.RepId) AS S
       ON s.productid = t.productid
 WHEN not matched BY source
  THEN DELETE;


Script to updating the existing records as Inactive with EndDate


MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS HashKey
    FROM dbo.Employee S1
      JOIN dbo.Product S2
         ON s1.EmpId = s2.RepId) AS S
         ON s.productid = t.productid
   WHEN matched and s.hashkey <> t.hashkey
    THEN update
       SET t.IsActive = 0, T.Enddate = GETDATE();


Script to update records for changes in Employee table and to insert new records for changes in Product table


MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS HashKey
FROM dbo.Employee S1
JOIN dbo.Product S2
ON s1.EmpId = s2.RepId) AS S
ON t.hashkey = s.hashkey
WHEN matched THEN UPDATE
SET t.[Productid] = s.[productid]
,t.[ProductType] = s.[ProductType]
,t.[AvailableQty] = s.[AvailableQty]
,t.[Price] = s.[Price]
,t.[Location] = s.[Location]
,t.[EmpName] = s.[EmpName]
,t.[Age] = s.[Age]
,t.[Designation] = s.[Designation]
,t.[CtNumber] = s.[CtNumber]
,t.[IsActive] = s.[IsActive]
,t.[StartDate] = s.[StartDate]
,t.[EndDate] = s.[EndDate]
,t.[HashKey] = s.[HashKey]
WHEN not matched
THEN INSERT  ([ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,[IsActive]
,[StartDate]
,[EndDate]
,[HashKey])
VALUES (s.[ProductId]
,s.[ProductName]
,s.[ProductType]
,s.[AvailableQty]
,s.[Price]
,s.[Location]
,s.[EmpName]
,s.[Age]
,s.[Designation]
,s.[CtNumber]
,s.[IsActive]
,s.[StartDate]
,s.[EndDate]
,s.[HashKey]);

Script to insert new records


MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId] 
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS HashKey
FROM dbo.Employee S1
JOIN dbo.Product S2
ON s1.EmpId = s2.RepId) AS S
ON s.productid = t.productid
WHEN not matched
THEN INSERT  ([ProductId] 
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,[IsActive]
,[StartDate]
,[EndDate]
,[HashKey])
VALUES (s.[ProductId] 
,s.[ProductName]
,s.[ProductType]
,s.[AvailableQty]
,s.[Price]
,s.[Location]
,s.[EmpName]
,s.[Age]
,s.[Designation]
,s.[CtNumber]
,s.[IsActive]
,s.[StartDate]
,s.[EndDate]
,s.[HashKey]);

Below is the result for the requirement.