‘MERGE’ statement is a new feature in SQL Server 2008. It can be used to perform insert, update and delete operation on a destination table simultaneously based on the results of a join with a source table. Well, it sounds like a bit confusing, but let's see an example on how it can help us.
Assume we have following two tables.
Both table are identical in structure (Structure does not need to be identical).
And we have to update the ‘STUDENT_A’ with the details at ‘STUDENT_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.
And to do the above operation use the following code:
**Please note that semicolon ‘;’ is mandatory.
So after executing the above code, and if you inspect the Table ‘A’, you can see that it’s updated the way we wanted.
And also you can use additional rules other than your condition. To illustrate that, first we insert a record to both the tables.
And using the following code you can remove the record with matches the condition and have the value 10.
And if you inspect the table A, you can see that it has the same following results: