MERGE Command in SQL SERVER

MERGE is one of the new features of SQL server. This comman is used for merging of DML commands in SQL. Ie UPDATE,INSERT and DELETE can do in a single statement using this Command. The following statements are explainig the real use of Merge command.

 INSERT INTO usedcars VALUES ( 1, 'Maruthy Swift','XXXXX',2000)

 INSERT INTO usedcars VALUES ( 2, 'Maruthy Swift','XXX11',2010)

 INSERT INTO usedcars VALUES ( 3, 'Maruthy Esteelo','UYUYU',2009)

 INSERT INTO usedcars VALUES ( 4, 'FORD Classic','kdsjfdhs',2011)

 INSERT INTO usedcars VALUES ( 5, 'Hundai Icon','XXsdf',2000)

 INSERT INTO usedcars VALUES ( 6, 'TATA Indica','TTTXX',2005)

 

 INSERT INTO usedcarprice VALUES (1,500000)

 INSERT INTO usedcarprice VALUES (3,50000)

 INSERT INTO usedcarprice VALUES (6,350000)

 INSERT INTO usedcarprice VALUES (5,255000) 

Result set of Usedcars table:-

 select * from usedcars

 

No	CarName	Ownername	Model
1	Maruthy Swift	XXXXX	2000
2	Maruthy Swift	XXX11	2010
3	Maruthy Esteelo	UYUYU	2009
4	FORD Classic	kdsjfdhs	2011
5	Hundai Icon	XXsdf	2000
6	TATA Indica	TTTXX	2005

Result set of usedcarprice

select * from usedcarprice

No	Price
1	500000
3	50000
6	350000
5	255000

Here I needs to do following modifications for Price details of Car price.

·         Remove all price records of 2005 Model.

·         Give discount of 10000 to all 2000 or less Model.

·         All non-priced vehicles are priced as 100000 as default

MERGE usedcarprice AS ucp

USING (SELECT NO,carname,model,ownername FROM usedcars) AS uc

ON ucp.no= uc.no

WHEN MATCHED AND uc.MODEL =2005 THEN DELETE

WHEN MATCHED AND uc.MODEL <=2000 THEN UPDATE SET UCP.PRICE = ucp.price -10000

WHEN NOT MATCHED THEN INSERT values(uc.no,100000);

After the Merge statement we can see the output of usedcarprice. No 1& 5 is reduced 10000 rs in their price. It delete 2005 model and newly added 2 and 4.

Result set of usedcarprice

select * from usedcarprice

No	Price
1	490000
3	50000
5	245000
2	100000
4	100000