Merge statement- A simple way for DML TIP#105

When, I first saw this statement a Hindi proverb  come to my mind which is “1 teer 2 nishane” . The meaning of this proverb means a single bow which hit two different aim in one shot.

So let me explain “Merge” statement in more detail. With the help of this Merge statement we can do insert ,update and delete with a single statement.

Let’s understand this by an example. Suppose we have two tables Source and Target as shown in below figure

Now these tables will have some data so lets write query to insert some pre pop data

Now what we want follow things

1) if  employeeId of source and target tables are not matched and if employee name starts with “S” in the source table  then insert the value in target table.  (means insert Sunil & steve’s records from source to destination table)

2) If employeeId of source and target tables are matched then update the Employee name from source table to target table  (if any record matched then update the employee name which not the case with current data)

3) If employeeId of source and target tables are not matched and Employee name in target table starts with “S” then delete that row from target table. (So Sunidhi’s row will be deleted from target table)

Let’s write Merge Statement  for all the 3 above work.

 

Now you observed that there is one more statements which is written explicitly apart from the condition which is OUTPUT clause which helps us to determine what operation is performed.

When you run this you will get following output

Now see the actual data in both the table.

 

So, a simple merge statement is doing all 3 operations like update, insert ,delete.

I hope this tip will help you some where.

Enjoy !!

RJ!!!