Sunday, August 11, 2013

MERGE is SQL 2008

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