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

Posted by Mr. JOYDEEP DAS