SQL Insert/Update using Merge for multiple scenarios

  • 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')

     

  • 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!

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • 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

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

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