MERGE is SQL 2008
The SQL server 2008 introduced a special TSQL statement called MERGE.
The MERGE statement is combining of INSERT/ UPDATE / DELETE statement. It is specially used to manipulate source table to target table operations in a single tatement.
The syntax is mentioned bellow:
MERGE <Target Table> AS mld
USING <Source Table SQL> AS mpd
ON mld.MemberID = mpd.MemberID
WHEN MATCHED AND <Specify Others Conditions if needed>
THEN DELETE
WHEN MATCHED
THEN UPDATE SET mld.UserPassword = 'DefaultPassword'
WHEN NOT MATCHED
THEN <Insert Satement>
Example :
INSERT INTO sourceTable
(Roll, StudentName, Class)
VALUES (1, 'Joydeep Das', 1),
(2, 'Palash Paul', 1),
(3, 'Sukamal Jana',1)
GO
INSERT INTO TargetTable
(Roll, StudentName, TotalGarde)
VALUES (1, 'Raja', 'A'),
(2, 'Palash Paul', 'B')
GO
MERGE TargetTable AS t
USING (SELECT Roll, StudentName, Class FROM SourceTable) AS s
ON s.Roll = t.Roll
WHEN MATCHED AND s.Roll>3
THEN DELETE
WHEN MATCHED
THEN UPDATE SET t.StudentName = s.StudentName
WHEN NOT MATCHED
THEN INSERT(Roll, StudentName, TotalGarde)
VALUES(s.Roll, s.StudentName, 'C');
GO
