Incremental Update in SQL using MERGE statement

In this blog, I would be taking about doing incremental update in SQL using the MERGE statement. The MERGE statement can be used for doing incremental updates in a relational table. (Note that technically the MERGE statement allows you to do a combination of UPDATE and INSERT (UPSERT) along with deletion of records if certain conditions are met. While incremental update loads only the difference or the delta between two tables, the MERGE statement is useful since many a times I may want to update an existing record in the target table and insert new records that do not exist in the target table.)

The below snapshot shows a staging table that I have created in Oracle. It has got six columns representing Account, Entity, Scenario, Month, Year and Amount. 

The next snapshot shows the final table where I was to push the data to from the staging table. It has the same columns as the staging table along with a LOGGED column which is a TIMESTAMP field to indicate when the record was logged in the final table. 

The next snapshot shows me inserting data into the staging table.

 

The contents of the staging table is as shown in the next snapshot. 

Now, this is my requirement. I need to push the data from the staging table to my final table. If the record does not exist in the table, I need to insert the record. However, if the record exists in the final table, I need to update the logged field in the final table to indicate that the record has come again in the staging table and hence has a new timestamp entry.

In order to do this, I will make use of the MERGE statement as shown in the below snapshot. 

In the above statement, I am merging the contents of the ESSBASE_STAGING table into the ESSBASE_FINAL table on the condition that the account, entity, scenario, month, year and amount fields are same. If they are same, I will update the logged field and set its value to the CURRENT_TIMESTAMP. If the value is not matched, then I will insert the record from ESSBASE_STAGING table into the ESSBASE_FINAL table.

The contents of the FINAL table after the MERGE statement is as shown below. Observe the LOGGED field. 

Keeping the contents of the staging and final table same, I run the MERGE statement once again. This is shown in the below snapshot. 

The contents of the FINAL table is as shown in the next snapshot. Observe that the LOGGED field has been updated for all the records to indicate the records exist in the staging and final table.

I now do an insert into the staging table as shown in the next snapshot.

Once the above record has been inserted, I delete all the previous records using a DELETE query shown in the next snapshot.  

The contents of the STAGING table is as shown in the next snapshot. 

I now the merge statement once again as shown in the next snapshot. 

The contents of the final table after running the MERGE statement is as shown below. Observe that only PLNEW record has been inserted. The timestamp of the other records has not changed since the UPDATE condition did not match.   

The MERGE statement saves multiple iterations of UPDATE and INSERT and combines these two into a single operation. In one of my next blogs, I would show how we can use the MERGE statement to do an incremental update in FDMEE using the Open Interface Adapter.