Merge Statement E.G

MERGE: Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. For example:

-- MERGE STATEMENT E.G

DECLARE @sCUSTOMER TABLE (ID INT, NAME VARCHAR(50), ADDD VARCHAR(50))

DECLARE @dCUSTOMER TABLE (ID INT, NAME VARCHAR(50), ADDD VARCHAR(50))

INSERT INTO @sCUSTOMER VALUES

                              (1, 'KRIS','HYDERABAD'),

                              (2,'KUMAR','HAYATHNAGAR'),

                              (3,'SAM','LONDON'),

                              (4,'TOM','NEWYORK')

 INSERT INTO @dCUSTOMER VALUES

                              (100,'MOON','PLANET')

 SELECT * FROM @sCUSTOMER -- 4 RECORDS AVAILABLE

 SELECT * FROM @dCUSTOMER -- 1 RECORDS

 MERGE @dCUSTOMER DW

 USING @sCUSTOMER DC

      ON DW.ID = DC.ID

      WHEN MATCHED THEN UPDATE

            SET ID = DC.ID,

                   NAME = DC.NAME,

                   ADDD = DC.ADDD

      WHEN NOT MATCHED THEN

                  INSERT  (ID, NAME, ADDD)

                  VALUES (DC.ID, DC.NAME, DC.ADDD)

      WHEN NOT MATCHED BY SOURCE

            THEN DELETE ;

  

 SELECT * FROM @sCUSTOMER -- 4 RECORDS AVAILABLE

 SELECT * FROM @dCUSTOMER -- 4  RECORDS AVAILABLE  BUT PLANET RECORD WILL BE  DELETED BCZ ID NOT MATCHING WITH SOURCE