MERGE STATEMENT.....IN SQL SERVER 2008

Hi Guys,

I m here once again to explore a one specialized statement called MERGE STATEMENT.

Basically when we go for any DML operations, we have to write separate coding for each and every operation like INSERT,DELETE,UPDATE but in the MERGE STATEMENT having the feature to include all the three operations in a single logic and make the data to be updated when its matches and inserts when not matches.

One of the main advantage in the MERGE STATEMENT is data is to be read and processed only once in a pass.

MERGE STATEMENT SYNTAX:

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias]
USING
ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
THEN ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ]
[ ]

[ OPTION ( [ ,...n ] ) ]

For Example:

use tempdb

GO

-- create the table with primary key

create table kingdom

(

id int primary key,

name nvarchar(20)

)

GO

--insert some records into the tables

insert into kingdom values(101,'king')

insert into kingdom values(102,'queen')

insert into kingdom values(103,'prince')

insert into kingdom values(104,'princess')

insert into kingdom values(105,'minister')

insert into kingdom values(106,'soldier')

GO

-- create another table with reference and insert some records into it

create table kingdom_details

(

id int references kingdom(id),

salary money

)

Go

insert into kingdom_details values(101,1000)

insert into kingdom_details values(102,850)

insert into kingdom_details values(103,700)

Go

--USING THE MERGE STATEMENT

-- DELETE THE RECORDS WHOSE SALARY GREATER THAN 900

--UPDATE THE RECORDS AND ADD 100 TO THE RECORDS IF EXISTS

--INSERT THE RECORDS

MERGE KINGDOM_DETAILS AS KD

USING (SELECT ID,NAME FROM KINGDOM) AS K

ON KD.ID = K.ID

WHEN MATCHED AND KD.SALARY > 900 THEN DELETE

WHEN MATCHED THEN UPDATE SET KD.SALARY = KD.SALARY + 100

WHEN NOT MATCHED THEN

INSERT(ID,SALARY) VALUES(K.ID,500);

GO

--retrive the original table records

select * from kingdom_details order by id