SQL Insert/Update using Merge for multiple scenarios

• vikasjagadale8

  Say Hey Kid

  Points: 704

  More actions

  November 25, 2021 at 10:12 am

  #3956791

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

   

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  November 25, 2021 at 1:23 pm

  #3956839

  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!

  ---

• Steve Collins

  SSCrazy

  Points: 2198

  More actions

  November 25, 2021 at 1:40 pm

  #3956853

  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