- We can merge data from a source table into a target table using merge statement. The practical use of merge statement in data warehousing and in OLTP (Online Transaction Processing).
- 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>: It is used to define the target table for merge statement. Here, we can also give alias name to the target table.
- USING <source_tablename>: It defines source table name for the operation.
- ON<merge condition>: In this clause, we specify a predicate for row matching between source table and target table.
- WHEN MATCHED then: It defines an action to take when a source row is matched with a target row. Target row is exit, so we cannot use insert action. Update and delete actions are allowed.
- WHEN NOT MATCHED BY target then: It defines which action to take when a source row is not matched by target row. We have to perform insert action because a row is not exist in target.
- WHEN NOT MATCHED BY source then: It defines which action to take when a target row is not matched by source row. We can perform either an update or delete action because target row is exist.
Name City 1 Anil
Name City 1 Atul
- Merge target as T
- using source as S
on T.Id= S.Id
when matched then
update set T.Name= S.Name
when not matched by target then
insert (Id, Name) values (S.Id, S.Name)
when not matched by source then
- Here, when we execute query it gives output like 2 rows affected.
- Now, execute below query to see the output.
- Select * from target;
Atul Anand 2 Nita
Keep visiting my site ask2tech.com for upcoming top technical article