SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The OUTPUT Clause for the MERGE Statements


The OUTPUT Clause for the MERGE Statements

Author
Message
Amarendra Reddy Thummeti
Amarendra Reddy Thummeti
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1216 Visits: 132
Comments posted to this topic are about the item The OUTPUT Clause for the MERGE Statements

Thanks,
AMAR
amarreddy23@gmail.com
yogesh.l.patil 36576
yogesh.l.patil 36576
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 25
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search