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.
Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH (
USING
ON
[ WHEN MATCHED [ AND
THEN
[ WHEN NOT MATCHED [ BY TARGET ] [ AND
THEN
[ WHEN NOT MATCHED BY SOURCE [ AND
THEN
[
[ OPTION (
Example:
Let’s create Student Details and StudentTotalMarks and inserted some records.
Student Details:
USE TestDataBase GO CREATE TABLE StudentDetails ( StudentID INTEGER PRIMARY KEY, StudentName VARCHAR(15) ) GO INSERT INTO StudentDetails VALUES(1,'Aditya') INSERT INTO StudentDetails VALUES(2,'MARK') INSERT INTO StudentDetails VALUES(3,'SAM') INSERT INTO StudentDetails VALUES(4,'JOHN') INSERT INTO StudentDetails VALUES(5,'GARY') GO
StudentTotalMarks:
CREATE TABLE StudentTotalMarks ( StudentID INTEGER REFERENCES StudentDetails, StudentMarks INTEGER ) GO INSERT INTO StudentTotalMarks VALUES(1,230) INSERT INTO StudentTotalMarks VALUES(2,255) INSERT INTO StudentTotalMarks VALUES(3,200) GO
In our example we will consider three main conditions while we merge this two tables. Delete the records whose marks are more than 250. Update marks and add 25 to each as internals if records exist. Insert the records if record does not exists. Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.
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
There are two very important points to remember while using MERGE statement.
- Semicolon is mandatory after the merge statement.
- When there is a MATCH clause used along with some condition, it has to be specified first among all other WHEN MATCH clause.
MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.
