MERGE STATEMENT IN SQL SERVER

• SQL MERGE STATEMENT WAS INTRODUCED IN SQL SERVER 2008.
• MERGE PERFORMS INSERT, UPDATE, OR DELETE OPERATIONS ON A TARGET TABLE BASED ONTHE RESULTS OF A JOIN WITH A SOURCE TABLE.

       

  SYNTAX:

    

                  MERGE  <HINT>  [INTO]   <TARGET-TABLE_NAME>

                  USING <SOURCE_TABLE_VIEW_OR_QUERY>

                   ON (<CONDITION>)

                  WHEN MATCHED THEN <UPDATE_CLAUSE>

                  WHEN NOT MATCHED THEN <INSERT_CLAUSE>;

---CONDITION MATCHES UPDATE WILL PERFOM THAT PARTICULAR RECORD.

---CONDITION NOT MATCHED INSERT RECORD IN A TABLE

---DELETE IS A OPTINAL.WHEN WE DELETE IN MATCHED CONDITION.

IMPORTANT NOTES:

•    SEMICOLON IS MANDATORY AFTER THE MERGE STATEMENT.

• WHEN THERE IS A MATCH CLAUSE USED ALONG WITH SOME CONDITION, IT HAS TO BE SPECIFIED FIRST AMONGST ALL OTHER WHEN MATCH CLAUSE. 

USES:

   

• USEFUL IN BOTH OLTP AND DATA WAREHOUSE ENVIRONMENTS 

          OLTP: MERGING RECENT INFORMATION FROM EXTERNAL SOURCE

            DW: INCREMENTAL UPDATES OF FACT, SLOWLY CHANGING DIMENSIONS.

EXAMPLES:

SOURCE TABLE:

CREATE TABLE STUDENT_DETAILS

(

       STU_ID INTEGER PRIMARY KEY,

       STU_NAME VARCHAR(15)

)

TARGET TABLE:

CREATE TABLE STUDENT_TOTAL_MARKS

(

      STU_ID INTEGER REFERENCES STUDENTDETAILS,

        STU_MARKS INTEGER

)

INSERT SOURCE TABLE DATA            INSERT TARGET TABLE DATA

STU_ID     STU_NAME                                             STU_ID    STU _MARKS   

    1                    SMITH                                                  1                   230

    2                    ALLEN                                                  2                   255

    3                     JONES                                                   3                  200

    4                   MARTIN

    5                    JAMES

In our example we will consider three main conditions while we merge this twotables.

     1.DELETE THE RECORDS WHOSE MARKS ARE MORE THAN 250.

     2.UPDATE MARKS AND ADD 25 TO EACH AS INTERNALS IF RECORDS EXIST.

      3.INSERT THE RECORDS IF RECORD DOES NOT EXISTS

MERGE  STUDENT_TOTAL_MARKS AS A

USING  (SELECT STU_ID,STU_NAME FROM STUDENT_DETAILS) AS B

ON A.STU_ID=B.STU_ID

WHEN MATCHED AND STU_MARKS>250 THEN DELETE

WHEN MATCHED THEN UPDATE  SET STU_MARKS=STU_MARKS+25

WHEN NOT MATCHED INSERT INTO (STU_ID,STU_MARKS)

VALUES(B.STU_ID,25);

OUTPUT:

IMPLEMENTING OUTPUT CLASS IN MERGE:


CREATE TABLE  BOOKINVENTORY  -- TARGET
(
  TITLEID INT NOT NULL PRIMARY KEY,
  TITLE NVARCHAR(100) NOT NULL,
  QUANTITY INT NOT NULL
    CONSTRAINT QUANTITY_DEFAULT_1 DEFAULT 0
);

CREATE TABLE   BOOKORDER  -- SOURCE
(
  TITLEID INT NOT NULL PRIMARY KEY,
  TITLE NVARCHAR(100) NOT NULL,
  QUANTITY INT NOT NULL
    CONSTRAINT QUANTITY_DEFAULT_2 DEFAULT 0
);
INSERT BOOKINVENTORY VALUES  (1, 'THE CATCHER IN THE RYE', 6),
                                                                       (2, 'PRIDE AND PREJUDICE', 3),
                                                                       (3, 'THE GREAT GATSBY', 0),
                                                                       (5, 'JANE EYRE', 0),
                                                                       (6, 'CATCH 22', 0),
                                                                       (8, 'SLAUGHTERHOUSE FIVE', 4);

INSERT BOOKORDER VALUES  (1, 'THE CATCHER IN THE RYE', 3),
                                                             (3, 'THE GREAT GATSBY', 0),
                                                             (4, 'GONE WITH THE WIND', 4),
                                                             (5, 'JANE EYRE', 5),
                                                             (7, 'AGE OF INNOCENCE', 8)


CAPTURE INSERT,UPDATE,DELETE:

DECLARE @MERGEOUTPUT TABLE
(
  ACTIONTYPE NVARCHAR(10),
  DELTITLEID INT,
  INSTITLEID INT,
  DELTITLE NVARCHAR(50),
  INSTITLE NVARCHAR(50),
  DELQUANTITY INT,
  INSQUANTITY INT
);

MERGE BOOKINVENTORY  AS  A
USING BOOKORDER AS B
ON BI.TITLEID = BO.TITLEID
WHEN MATCHED AND
  BI.QUANTITY + BO.QUANTITY = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE
  SET BI.QUANTITY = BI.QUANTITY + BO.QUANTITY
WHEN NOT MATCHED BY TARGET THEN
  INSERT (TITLEID, TITLE, QUANTITY)
  VALUES (BO.TITLEID, BO.TITLE,BO.QUANTITY)
WHEN NOT MATCHED BY SOURCE
  AND BI.QUANTITY = 0 THEN
  DELETE
OUTPUT
    $ACTION,
    DELETED.TITLEID,
    INSERTED.TITLEID,
    DELETED.TITLE,
    INSERTED.TITLE,
    DELETED.QUANTITY,
    INSERTED.QUANTITY
  INTO @MERGEOUTPUT;

SELECT * FROM BOOKINVENTORY;

SELECT * FROM @MERGEOUTPUT