Using Merge Option While Loading Data

While Loading from Source to Target its not necessary that all the records are fresh inserts. For example when we load the data for the transactions table there will be both insert as well as Updates. There will both new transactions as well as old transactions to be updated. If ETL tool is Informatica, we can use Update strategy for implementing both Inserts and Updates. The same we can implement using MERGE SQL statement. Now we will see how we can use MERGE for Update and Insert Process:

MERGE INTO Customer USING new_Customer

ON (Customer.customer_id = new_Customer.customer_id)

WHEN MATCHED THEN

UPDATE SET Customer.name = new_Customer.name, Customer.loc = new_Customer.loc

WHEN NOT MATCHED THEN INSERT (name,loc)

VALUES (new_Customer.name, new_Customer.loc);

Insert will insert the entire record from new Customer table into the actual customer table. Update will update the columns loc and name for all those record which already exists in the customer table.

In cases where we don’t want insert to happen in the target table and only updates then in such case we can omit the Insert clause from the MERGE statement. And in cases where we need only insert to happen then we can omit the Update clause.