Scenario (Update and Insert Using HashKey)

Scenario: The details of products are updated in a table with StartDate, EndDate as null since the product is active and IsActive as 1 since the product is still active. 


Condition: When the details of a product is updated in source, then a new row should be updated for the same product in the destination and existing row should be udpated with EndDate as when the product updated second time and IsActive should be udpated as 0 which the product is not active.


SQL Update Script


MERGE dbo.ProductCodeHistory AS T1
USING (SELECT  ProductCodeID
               ,BINARY_CHECKSUM([ProductCodeId], [ProductDefinitionId], [ProductCodeType],      CodeValue, [IsActive], [IsDeleted]) AS HashKey
              , ? AuditTaskID
FROM ProductCode
WHERE ProductCodeType 
IN ('Cable', 'Wire', 'Flange', 'Ring') S1
ON (S1.ProductCodeID = T1. ProductCodeId)
WHEN MATCHED AND S1.HashKey <> T1.Hashkey  
THEN UPDATE SET T1.IsActive = 0, T1.EndDate = GETDATE(), T1.AuditTaskID = S1.AuditTaskID;
SELECT ? = @@ROWCOUNT;


SQL Insert Script


MERGE dbo. ProductCodeHistory AS T1
USING (SELECT   ProductCodeID
        ,[ ProductDefinitionId]
        ,[ ProductCodeType]
        ,[CodeValue]
        ,[IsActive] CodeIsActive
        ,IsDeleted
        ,BINARY_CHECKSUM([ProductCodeId], [ ProductDefinitionId], [ ProductCodeType],  CodeValue, [IsActive], [IsDeleted]) HashKey
       , ? AuditTaskID
FROM  ProductCode
WHERE  ProductCodeType 
IN ('Cable', 'Wire', 'Flange', 'Ring') S1
ON  S1.HashKey = T1.Hashkey  AND T1.IsActive = 1
WHEN NOT MATCHED THEN
INSERT ( ProductCodeId
,ProductDefinitionId
,CodeType
,CodeValue
,CodeIsActive
,IsDeleted
,IsActive
,StartDate
,AuditTaskID)
VALUES
(S1. ProductCodeID
,S1.[ProductDefinitionId]
,S1.[ProductCodeType]
,S1.[CodeValue]
,S1.CodeIsActive
,S1.IsDeleted
,1
,Getdate()
,S1.AuditTaskID
);
SELECT ? = @@ROWCOUNT;