Merge Statement

Question

hi anyone rectify my doubt i have only one table in my master db can i use Merge statement in that table it is possible if possible please send me query mostly iam try it iam not found out

Piyush Pansuriya · Answer

Hello, Try this : MERGE dbo.student AS T USING dbo.student AS S ON (T.ID = S.ID) WHEN MATCHED AND (T.ID = S.ID) THEN UPDATE SET T. Name = S. Name , T.Address = S.Address WHEN NOT MATCHED BY TARGET THEN INSERT (ID, Name , Address) VALUES (S.ID, S. Name , S.Address) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $ action , inserted.*, deleted.*;

Piyush Pansuriya · Answer

Hello, Yes it is possible to use same table in both source and destination. But you can not get result. MERGE statement match/unmatch the rows and perform the operation, So you will get '0 rows affected ' as reult. For more detail go through the below link : https://stackoverflow.com/questions/10794870/using-sql-server-merge-command-with-same-source-target-table

Piyush Pansuriya · Answer

Hello, MERGE statement is used to modify data in a target table based on data in a source table. So, its necessary to have two tables.

Amudhan Kuppan · Answer

hi piyush yes already iam got it yes u r right iam not get result 0 rows affected this is a result thank you for your help but last one doubt piyush i will send query for merge statement you see and after iam give one requirement mostly iam tried but iam not found it please help me piyush use master Go MERGE INTO dbo.student AS S USING dbo.Student AS D ON S.id = D.id WHEN MATCHED AND (S.name != D.name OR S.Address!= D.Address) THEN UPDATE SET S.name = D.name,S.Address = D.Address WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (D.id, D.name, D.Address) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT D.id,$action,inserted.id, deleted.id; this is my query for merge statement use one table as a source and destination my requirement is: please check with name and insert and update do not use id. , please correct the script and send me back

Amudhan Kuppan · Answer

Hi piyush today iam got new idea can i use same table as Source and Destination it is possible for to create merge statement please send me query piyush

Debasis Saha · Answer

Basically Merge Statement can perform both Insert, Update & Delete operation in a single statement with different condition at a time. But also we can use Merge Statement for any one of the above operation mode individually.

Amudhan Kuppan · Answer

thank you piyush

Amudhan Kuppan · Answer

ok piyush thanks