Merge

Question

Merge

peacesells

SSCrazy

Points: 2882
More actions
May 15, 2012 at 2:35 pm

#258271

Is 'TARGET' and 'SOURCE' reserved keyword in SQL server for merge statement?
I was trying
MERGE TABLEA AS T USING TABLEB AS S
ON S.ID=T.ID
WHEN MATCHED AND T.ProductName <> S.ProductName
OR T.Rate <> S.Rate THEN
UPDATE SET T.ProductName = S.ProductName,
T.Rate = S.Rate
WHEN NOT MATCHED BY T THEN --IT DOESN'T LET ME DO THIS
INSERT (ID, ProductName, Rate)
VALUES (S.ID, S.ProductName, S.Rate)
WHEN NOT MATCHED BY S THEN --IT DOESN'T LET ME DO THIS
DELETE;
-- BUT IF I START THE MERGE STATEMENT LIKE THIS
MERGE TABLEA AS TARGET USING TABLEB AS SOURCE -- THEN I CAN DO THE ABOVE, WHAT AM I MISSING??
TARGET.ID=SOURCE.ID

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

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

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

Yes - when you use the 'WHEN NOT MATCHED BY' clause, you have to use the keywords 'SOURCE' or 'TARGET'. However, it's ok to alias your actual table names like you did:

MERGE TABLEA AS T

USING TABLEB AS S

ON S.ID=T.ID

http://technet.microsoft.com/en-us/library/bb510625.aspx

_________________________________
seth delconte
http://sqlkeys.com

peacesells SSCrazy Points: 2882 More actions · Answer 2

seth delconte (5/15/2012)
Yes - when you use the 'WHEN NOT MATCHED BY' clause, you have to use the keywords 'SOURCE' or 'TARGET'. However, it's ok to alias your actual table names like you did:
MERGE TABLEA AS T
USING TABLEB AS S
ON S.ID=T.ID
http://technet.microsoft.com/en-us/library/bb510625.aspx

Thanks Seth. I need to Delete,Inserts and Update which means im gonna need to use 'WHEN NOT MATCHED BY' which follows by having to use keywords 'Source' and 'Target' I guess.

Merge

Cookies on SQLServerCentral