• HI,
    My question is regarding MERGE from SQL server. (Not exactly related output clause in Merge).
    So, sorry in advance if I am disturbing this topic.

    ------------------------

    I am trying to get the data from One table to 2 temp table with different conditions.
    Then update Quantity using merge 

    but it is giving some syntax error.
    Please find my code snippet.

    ///////////////////

    Select * INTO #DT0604_MAX_TARGET from DT0604
      where WID = @strWID
      AND NO = (SELECT MAX(NO) FROM DT0604 where WID = @strWID AND NO <> -1)
     
      /*All data less than MAX number in Source table.*/

      Select WID,ACD_FL,DATA_NO,SEQ_NO,GCT_COMP_NO,SUM(QTY) AS QTY INTO #DT0604_MAX_SOURCE from DT0604
      where WID = @strWID
      --AND NO = @str2ndMax_AkajiNo
      /*#DT0604_MAX_TARGET Has max Akaji data.
      Consider all Akaji data in source table except max Akaji data.*/
      AND NOT EXISTS
      (
       SELECT 'X' from #DT0604_MAX_TARGET
       where #DT0604_MAX_TARGET.WID = DT0604.WID
       AND #DT0604_MAX_TARGET.NO = DT0604.NO
      )
      GROUP BY WID,ACD_FL,DATA_NO,SEQ_NO,GCT_COMP_NO

      MERGE #DT0604_MAX_TARGET AS TARGET /* Syntax error here according to SQL*/
      USING #DT0604_MAX_SOURCE AS SOURCE
      ON
      (
       Target.WID = Source.WID AND
       Target.ACD_FL = Source.ACD_FL AND
       Target.DATA_NO = Source.DATA_NO AND
       Target.SEQ_NO = Source.SEQ_NO AND
       Target.GCT_COMP_NO = Source.GCT_COMP_NO
      )

      WHEN MATCHED THEN
      UPDATE SET TARGET.QTY += SOURCE.QTY

      OUTPUT $action,
      DELETED.WID AS Target_WID,
      DELETED.NO AS Target_NO,
      DELETED.ACD_FL AS Target_ACD_FL,
      DELETED.GCT_COMP_NO AS Target_GCT_COMP_NO,
      INSERTED.WID AS Source_WID,
      INSERTED.NO AS Source_NO,
      INSERTED.ACD_FL AS Source_ACD_FL,
      INSERTED.GCT_COMP_NO AS Source_GCT_COMP_NO;

      Select * from #DT0604_MAX_TARGET
    //////////////////

    I am not sure where the syntax is going wrong.
    To my surprise if I execute the code in separate parts then it is executed successfully.

    Thank you in advance.