Merge Statement

Amudhan Kuppan

8y920SQL

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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Pansuriya8y ago
Accepted 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.*;
Amudhan Kuppan8y ago
thank you piyush
Amudhan Kuppan8y ago
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
Piyush Pansuriya8y ago
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
Amudhan Kuppan8y ago
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 Saha8y ago
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 Kuppan8y ago
ok piyush thanks
Piyush Pansuriya8y ago
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.