SQL Merge Transformation (Delete, Update and Incremental Load)

This is a complete picture and script for SQL Merge Transformation (Update and Incremental Load)

Below is the step to get the MaxAuditId from the Staging database (this task is to maximize performance)

Get the SQL task as showed below:

Get in to the task and add variables as showed below. 

For the above task, variables should be created as shown below:

Get into the SQL Task Editor, in the Sql statement option, do update the below query to get the MaxAuditTaskId from the Staging DB table

Select ? = IsNull(Max(MaxAuditTaskId),0) from dbo.AuditTaskSummary
where TableName = ? 


Now the step is to create a SQL task for Update and Incremental Load


Get the SQL task as showed below:

Get in to the task and add variables as showed below:

Get into the SQL Task Editor, in the Sql statement option, do update the below query for update and Incremental load:

SET NOCOUNT ON;
DECLARE 
        @MaxAuditTaskId bigint,
        @AuditTaskId bigint;
SET @MaxAuditTaskId =?;
SET @AuditTaskId = ?;


MERGE [dbo].[ConstituentHistory] as myTarget 
USING ( 
SELECT 
        [ConstituentHistoryKey] 
       ,[ConstituentDefinitionId] 
       ,[ConstituentDefinitionRollupId] 
       ,[AccountStatus] 
       ,[StartDate]
       ,[EndDate]
       ,[IsActive]
FROM s_ConstituentHistory
WHERE [AuditTaskId] > @MaxAuditTaskId
) AS mySource 
ON 
mySource.[ConstituentDefinitionId] = myTarget.[ConstituentDefinitionId]

WHEN MATCHED THEN 
UPDATE SET 
        
       myTarget.[ConstituentDefinitionRollupId] = mySource.[ConstituentDefinitionRollupId]
      ,myTarget.[AccountStatus] = mySource.[AccountStatus]
      ,myTarget.[StartDate] = mySource.[StartDate]
      ,myTarget.[EndDate] = mySource.[EndDate]
      ,myTarget.[IsActive] = mySource.[IsActive]
             
WHEN NOT MATCHED THEN 
INSERT 
(      
       [ConstituentDefinitionId]
      ,[ConstituentDefinitionRollupId]
      ,[AccountStatus]
      ,[StartDate]
      ,[EndDate]
      ,[IsActive]
      ,[AuditTaskId]
      )
VALUES(
       mySource.[ConstituentDefinitionId]
      ,mySource.[ConstituentDefinitionRollupId]
      ,mySource.[AccountStatus]
      ,mySource.[StartDate]
      ,mySource.[EndDate]
      ,mySource.[IsActive]
      ,@AuditTaskId
      );
            
 SELECT  ?=@@ROWCOUNT;