SQL Insert/Update using Merge for multiple scenarios

Question

SQL Insert/Update using Merge for multiple scenarios

Say Hey Kid

Points: 704

November 25, 2021 at 10:12 am

I have 4 scenarios to cover so I am using the below Merge query in SQL Server 2016. Using Merge query, scenarios 1,2,3 work but scenario 4 does not work. Can someone please help? also, I am ok to use other approaches as well like JOINS if it covers all 4 scenarios.

Please note Source table i.e. temp_Wrike_Kimble can have 1 or many records which need to be updated/insert into target table (Wrike_Task_Kimble_Log)

Link: DBFiddle

--Create a target table
CREATE TABLE Wrike_Task_Kimble_Log
(
   Taskid varchar(50) NOT NULL,
   Wrike_Update_Task_URL varchar(max),
   ActualMargin float,
   BudgetMargin float,
   IsProcessed bit default 0,
   Rootpid varchar(50)
) 


--Create source table
CREATE TABLE temp_Wrike_Kimble
(
   Taskid varchar(50) NOT NULL,
   Wrike_Update_Task_URL varchar(max),
   ActualMargin float,
   BudgetMargin float,
   IsProcessed bit default 0,
   Rootpid varchar(50)
)

Merge Query:

MERGE Wrike_Task_Kimble_Log AS TARGET
USING temp_Wrike_Kimble AS SOURCE 
ON (TARGET.TaskId = SOURCE.TaskId)

WHEN MATCHED AND TARGET.Rootpid <> SOURCE.Rootpid AND TARGET.IsProcessed = SOURCE.IsProcessed 
THEN UPDATE SET TARGET.Rootpid = SOURCE.Rootpid

--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid) 
VALUES (SOURCE.TaskId, SOURCE.Wrike_Update_Task_URL,SOURCE.ActualMargin,SOURCE.BudgetMargin,SOURCE.IsProcessed,SOURCE.rootpid)

OUTPUT $action,


INSERTED.TaskId AS TargetTaskId, 
INSERTED.Wrike_Update_Task_URL AS TargetWrike_Update_Task_URL,
INSERTED.ActualMargin AS TargetActualMargin, 
INSERTED.BudgetMargin AS TargetBudgetMargin,
INSERTED.IsProcessed AS TargetIsProcessed,
INSERTED.Rootpid AS TargetRootpid;

SELECT @@ROWCOUNT;

Scenario 1: If in the first run, the record was not processed in target as (IsProcessed= 0), then in next run for the same record, only UPDATE the rootpid of target table to 'test2'

    --Insert records into the target table
    INSERT INTO Wrike_Task_Kimble_Log (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid)
    VALUES
     ('T1',NULL,10,5,0,'test1')

--Insert records into the source table
INSERT INTO temp_Wrike_Kimble (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid)
VALUES
   ('T1',NULL,10,5,0,'test2')

Scenario2: If a record is already successfully processed in target (IsProcessed= 1), then in the next run, the record having the same (TaskId AND ActualMargin AND BudgetMargin) will be ignored i.e. neither UPDATE / INSERT in the target table

--Insert records into target table
INSERT INTO Wrike_Task_Kimble_Log (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid)
VALUES
 ('T1','www.url',10,5,1,'test1')


--Insert records into source table
INSERT INTO temp_Wrike_Kimble (TaskId,Wrike_Update_Task_URL,ActualMargin,IsProcessed,Rootpid)
VALUES
   ('T1',NULL,10,5,0,'test2')

Scenario3: If a record is new (TaskId) i.e. which does not exist in Target then simply INSERT new record from Source to Target.

--Insert records into target table
INSERT INTO Wrike_Task_Kimble_Log (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid)
VALUES
 ('T1','www.url',10,5,1,'test1')


--Insert records into source table
INSERT INTO temp_Wrike_Kimble (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid)
VALUES
  ('T2',NULL,90,80,0,'test1')

Scenario4: If for a TaksId which is already processed in Target (IsProcessed= 1), but its (ActualMargin OR BudgetMargin) is changed in Source table, then INSERT it as new record from Source to Target

--Insert records into target table
INSERT INTO Wrike_Task_Kimble_Log (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid)
VALUES
 ('T1','www.url',10,5,1,'test1')


--Insert records into source table
INSERT INTO temp_Wrike_Kimble (TaskId,Wrike_Update_Task_URL,ActualMargin,BudgetMargin,IsProcessed,Rootpid)
VALUES
 ('T1',NULL,10,2,0,'test2')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

I can't see any easy way of accommodating scenario four in a MERGE, because MERGE does not allow an INSERT in the case of a match.

You could experiment with changing the match condition to specifically exclude scenario four:

ON (TARGET.TaskId = SOURCE.TaskId and NOT (TARGET.ActualMargin = SOURCE.ActualMargin AND TARGET.BudgetMargin = SOURCE.BudgetMargin ))

But that feels a bit dirty and not very pleasant to maintain!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2174 More actions · Answer 2

What is the primary key of the target table? With Scenario4 you're inserting a new row which would duplicate (or more?) the TaskId. Once the TaskId is non-unique then further JOIN'ing on it could become a problem. Also, there are many issues with MERGE in general. It's non-atomic, it doesn't use indexes to apply the ON condition, and others. The only way for MERGE to avoid a full table scan is to use a CTE. With MERGE an explicit transaction with try/catch and SET XACT_ABORT ON are a minimum imo. Better to split into 2 (or more) statements of INSERT/UPDATE imo

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können