Tip Of The Day, Sql Merge Statement

[Yevadu]

One Statement for INSERT, UPDATE, DELETE  

 

In earlier versions of SQL Server we had to write separate statements for Insert,update and Delete based on certain conditions but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

 

MERGE StudentTotalMarks AS stm

USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO

 

Detailed explanation in available in my blog below

 

http://vsaditya.blogspot.com/2014/04/sql-server-2008-merge-statement-one.html

 

[Mahesh_Fan]

telsindhey but thanks mayya for sharing the knowledge...

[Suhaas]

Good post bhayya.

[powerstar02]

telsindhey but thanks mayya for sharing the knowledge...

lol bob...

[ghazni]

GP... Na patha project lo they used this merge to migrate the data from oracle to Sql. 

[Mahesh_Fan]

lol bob...

pulka na yala

[mtkr]

bl@st