update,insert,delete query in a table

Amudhan Kuppan

8y722SQL

anyone please help me

my requirement is:

now create table and do that if exists than update not exists insert and record exists in destination table and not exists in source table please delete.

my source and destination table is Student table

that Student table Structure is :

ID int not null, name varchar(50),address varchar(50)

please help me and send me query

8 Replies

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

Piyush Pansuriya8y ago
Accepted answer
Hello,
Go through the below link :

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/
Amudhan Kuppan8y ago
thank you piyush
Amudhan Kuppan8y ago
no change piyush
same as iam got update only
please send me query
Amudhan Kuppan8y ago
ok thank you piyush
Piyush Pansuriya8y ago
Hello,
May be your data of both table not mached.
Once try to change the target table to souce and source to target, If helpful..
Amudhan Kuppan8y ago
hi piyush
iam try this code
but it only give result update
i need all scenario that is insert,update,delete all are covered
wait i will send screenshot
Amudhan Kuppan8y ago
ok piyush thank you
Piyush Pansuriya8y ago
Hi,
Already Given this Solution.
MERGE dbo.student_1 AS T
USING dbo.student_2 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.*;