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.