Merge statement sql server 2008

Merge statement sql

Merge statement is used to insert and update existing conditions data based on conditions whether they are met or not.

Facts about Merge statement:

• We can perform atomic statements (Insert,delete,update) based on conditions
• End of merge statement contains semicolon mark (;)
• Merger is defined by ANSI SQL, works in other database platforms as well
• Contains conditions as MATCHED and NOT MATCHED

CREATE TABLE STUDENT (

Student_Id int,

First_Name  VARCHAR(20),

Last_Name   VARCHAR(20),

Branch_No     int,

Fees      int);

INSERT INTO STUDENT VALUES (1, 'Neil', 'Sarkar', 11, 100000);

INSERT INTO STUDENT VALUES (2, 'deepak', 'Mishra', 52, 100000);

INSERT INTO STUDENT VALUES (3, 'Neha', 'Kumari', 52, 50000);

INSERT INTO STUDENT VALUES (4, 'Deepa', 'Sarkar', 52, 40000);

INSERT INTO STUDENT VALUES (5, 'Mukul', 'Bharti', 52, 30000);

INSERT INTO STUDENT VALUES (6, 'Vipul', 'Dalvi', 52, 70000);

INSERT INTO STUDENT VALUES (7, 'Michael', 'Puling', 52, 90000);

CREATE TABLE SCHOLARSHIP (

Student_Id int, Scholarship int DEFAULT 200);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (1);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (2);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (4);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (6);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (7);

MERGE [SCHOLARSHIP] AS S

USING

(

  SELECT Student_Id, Fees

  FROM STUDENT

  WHERE Branch_No =52) as E

ON (S.Student_Id = E.Student_Id)

WHEN MATCHED THEN

  UPDATE SET S.Scholarship = E.Fees * 10

WHEN NOT MATCHED THEN

  INSERT (Student_Id, Scholarship)

  VALUES (E.Student_Id, E.Fees * 0.6);