MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data 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 inone pass of database table. This is quite an improvement in performance of database query.
Assume we have following two tables.
- Table_A
- Table_B
Both table are identical in structure (Structure does not need to be identical).
Table_A
Table_B
And we have to update the ‘Table_A’ with the details at ‘Table_B’. We need to compare and if student ID’s are matched, ‘A’ table should be updated with the ‘B’ table. And if the ID’s in ‘B’ Table are new then we have to insert those to the ‘A’ table.
So using the ‘MERGE’ statement we can achieve this in one execution.
Syntax:
MERGE <Target> [AS T] USING <Source> [AS S] ON <Condition> [WHEN MATCHED THEN <Execution>] [WHEN NOT MATCHED BY TARGET <Execution>] [WHEN NOT MATCHED BY SOURCE <Execution>]
And to do the above operation use the following code:
MERGE Table_A AS TA USING Table_B AS SB ON TA.ID = SB.ID WHEN MATCHED THEN UPDATE SET TA.AGE = SB.AGE WHEN NOT MATCHED THEN INSERT (ID, FNAME, LNAME, AGE) VALUES(SB.ID,SB.FNAME,SB.LNAME,SB.AGE);
So after executing the above code, and if you inspect the Table ‘A’, you can see that it’s updated the way we wanted.
