Merge in Oracle

Merge in pl/sql:-

Scenario:-
 

Your requirement is to compare two tables and update one of those tables if same records are present. In simple words, you have two tables table 1 and table 2. Both the tables almost contain same data, at the same time table 1 contains 10 records more than the table 2. So you need to update table 2 so it will hold data same as table 1. To do this, we can use MERGE to compare and insert or update records based on condition specified.

What merge is?

The SQL MERGE command is combination of sequential conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as ‘Upsert’ functionality. ‘Upsert’ is the combination of Update or Insert.

Syntax for Merge statement is as below:-

   MERGE INTO TABLE_NAME USING TABLE_REFERENCE ON (CONDITION)

   WHEN MATCHED THEN

   UPDATE SET COLUMN1 = VALUE1 [, COLUMN2 = VALUE2 ...]

   WHEN NOT MATCHED THEN

   INSERT (COLUMN1 [, COLUMN2 ...]) VALUES (VALUE1 [, VALUE2 ...

HERE,

INTO Clause - The INTO clause is used to specify the target table into which you are
inserting or updating.

USING Clause - The USING clause specifies the source of the data to be updated or inserted. The source for a MERGE statement can be a table, view, or the result of a sub-
query.

ON Clause - The ON clause specifies the condition that the MERGE operation uses to determine whether it updates or inserts. When the search condition results to true, Oracle updates the row in the target table with corresponding data from the MERGE source. If no rows satisfy the condition, then Oracle inserts the row into the target table based on the corresponding MERGE source row.

merge_update_clause - The merge_update_clause is used to specify the update column values of the target table. Oracle performs the specified update if the condition of the ON clause is true. As with any normal update, when the update clause is executed, all update triggers defined on the target table are fired.

Let’s have an example of merge:-

Create a table called Test:-

Here I have not specified any constraint but you may have constraint on your table which can be called well structured table.

Insert some values in the table TEST:-

Now create a Source table Test_1 and insert some data into it as shown below:-

Above we have created a table same as Table test and also inserted some new records into it.

So there are now 10 records in the table TEST_1 and 5 records in TEST table.

Now our requirement is to update the table TEST and insert the values which are not present in TEST table and update if its present. We will use MERGE statement below to fulfill the requirement:-

CREATE OR REPLACE PROCEDURE ORACLE_MERGE AS

UPDATE_CNT NUMBER(6):= 0;    ------VARIABLE DECLARED HARE WHICH WILL HOLD COUNT OF INSERT AND UPDATE.

INSERT_CNT NUMBER(6):= 0;

BEGIN

DBMS_OUTPUT.PUT_LINE('START >> ' || TO_CHAR(SYSDATE,'DD-MON-YY HH:MI:SS AM') );

MERGE INTO TEST A USING TEST_1 B

ON (A.EMPNO=B.EMPNO)     ------- YOU CAN SPECIFY HERE AS MUCH PRIMARY KEYS AS YOU HAVE.

WHEN MATCHED THEN

UPDATE SET A.EMPNO=B.EMPNO,

A.EMPTYPE=B.EMPTYPE,

A.SALARY=B.SALARY

WHEN NOT MATCHED THEN

INSERT (EMPNO, EMPTYPE, SALARY)

VALUES (B.EMPNO,

B.EMPTYPE,

B.SALARY);

INSERT_CNT:= MERGE_INSERTUPDATE_RECORD.GET_MERGE_INSERT_COUNT;

UPDATE_CNT:= MERGE_INSERTUPDATE_RECORD.GET_MERGE_UPDATE_COUNT (SQL%ROWCOUNT);

DBMS_OUTPUT.PUT_LINE ('ROWS UPDATED >> ' || UPDATE_CNT);

DBMS_OUTPUT.PUT_LINE ('ROWS INSERTED >> ' || INSERT_CNT);

DBMS_OUTPUT.PUT_LINE ('END >> ' || TO_CHAR (SYSDATE,'DD-MON-YY HH:MI:SS AM') );

DBMS_OUTPUT.PUT_LINE ('PART PROCESS END >> ' || TO_CHAR (SYSDATE,'DD-MON-YY HH: MI: SS AM'));

MERGE_INSERTUPDATE_RECORD.RESET_COUNTERS;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('SOME ERROR OCCURED');

END;

Select the count by querying test and test_1 table, there must be 5 and 10 records respectively:-

Now our requirement is to update or insert records from TEST_1 table. So execute the above created procedure.

Execute Procedure as shown below:-

After execution it clearly shows that rows updated and rows inserted are 5 each which are correct.

Now again Confirm the changes by querying test table:

 

By using above procedure we have done inserting or updating records by oracle Merge statement.




Read Also:-  Using Loops