In this, post we will discuss about merge statement in SQL server 2016. It is used in many real time applications.
- Using MERGE statement, we can merge data from a source table into a target table. The practical use of merge statement in OLTP (Online Transaction Processing) and in data warehousing.
- Assume the situation when your server is off and any user change their information or new user register at that time store this information into source table and after when the server is on apply the merge operation from source to target table so target table will be loaded with new data.
Merge into <target_tablename> as T
Using <source_tablename> as S
When matched then
<update or delete action>
When not matched by target then
<action must be insert>
When not matched by source then
<update or delete action>;
- MERGE into <target_tablename>: This clause is used to define the target table for merge statement. We can also give alias name to the target table.
- USING <source_tablename>: This clause defines the source table name for the operation. The USING clause is similar to FROM clause in a select query means we can define a derived table or a common table expression(CTE).
- ON<merge condition>: In this clause, we specify a predicate for row matching between source and target table.
- WHEN MATCHED then: This clause defines an action to take when a source row is matched with a target row. we cannot use insert action because a target row is exit. The two actions allowed to perform are update and delete.
- WHEN NOT MATCHED BY target then: This clause defines which action to take when a source row is not matched by target row. Because a row not exit in target we have to perform insert action.
- WHEN NOT MATCHED BY source then: This clause defines which action to take when a target row is not matched by source row. Here target row is exists so we can either perform a update or delete action.
Merge stutarget as t
using stusource as s
when matched then
update set t.sname=s.sname
when not matched by target then
insert (id, sname) values(s.id, s.sname)
when not matched by source then
In this example source and target table has two rows. Here id 1 of source and target table matched so we use update action to update the ename of target table. And id 3 is their in target but not in the source table so we use delete action it delete row that have id 3 from target table. Id 2 exist in the source but not in the target so we use insert action to insert row that have id 2 .
You can also by SQL 70-761 book write by me from Amazon.