Delta load logic using oracle merge sql statement

Question

I have table TEST_RUA where i am importing data daily. I am using Truncate and Insert logic for this table. Now i have another table TEST_RUA_MER same like table TEST_RUA where i want to apply Delta load logic. There is always unique combination of column values ID_LL, ID_UU, TKR in both table.

The Delta logic should be:

For Update: i want to compare the data from table TEST_RUA_MER and TEST_RUA and then update the table TEST_RUA_MER only when there is unique combination of column values ID_LL, ID_UU, TKR exist in both table and when there is change in any field value in table TEST_RUA.We can also introduce new column called status and update the status as 'UPD' for this rows so that we can filter out data in View if required.

For Insert: if there is no unique combination of column values ID_LL, ID_UU, TKR exist in table TEST_RUA_MER but exist in table TEST_RUA then insert the data into table TEST_RUA_MER.

Delete: The Delete statement can be done separately. We have to use Delete statement to delete the data from table TEST_RUA_MER which does not exist in table TEST_RUA while using unique combination of columns ID_LL, ID_UU, TKR and also delete data from table TEST_RUA_MER which is equal in table TEST_RUA. So in table TEST_RUA_MER i just only want to keep updated and new data from table TEST_RUA. If Delete not possible we can also create just View to filter out the data if possible.

Can it be achieved with single merge statement and delete statement separately or is there any other logic we can implement? I dont want to use pl/sql for this logic. But i can also use Sql View for any comparision condition because at the end i just want to generate csv report with the delta load table.

Below is sql fiddle for create and insert statement: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6288a8b83149d3d543a776b9690bb59f

Below is the sql statements:

Create statement for TEST_RUA table:

Create table TEST_RUA (CLASS VARCHAR2(100), ID_LL VARCHAR2(100), ID_UU VARCHAR2(100), TKR VARCHAR2(100), NAME VARCHAR2(100))

Insert statement for TEST_RUA table:

INSERT INTO TEST_RUA VALUES ('Bond', 'BERF', 'GV9999B12M1', 'TKXX', 'TES_RES');
INSERT INTO TEST_RUA VALUES ('Bond', 'BERT', 'FV9999B12M3', 'BURR', 'PRS_RES');
INSERT INTO TEST_RUA VALUES ('Bond', 'BREG', 'TV9999B12M4', 'CVKR', 'FRTG_OP');
INSERT INTO TEST_RUA VALUES ('Bond', 'BREG', 'SQTUREGBFNO', 'LRQU', 'BEGT_TU');

Create statement for TEST_RUA_MER table:

Create table TEST_RUA_MER (CLASS VARCHAR2(100), ID_LL VARCHAR2(100), ID_UU VARCHAR2(100), TKR VARCHAR2(100), NAME VARCHAR2(100));

Insert statement for TEST_RUA_MER table:

    INSERT INTO TEST_RUA_MER VALUES ('Bond', 'BERF', 'GV9999B12M1', 'TKXX', 'TES_RES');
    INSERT INTO TEST_RUA_MER VALUES ('Bond', 'BERT', 'FV9999B12M3', 'BURR', 'PRS_RES');
    INSERT INTO TEST_RUA_MER VALUES ('Bond', 'BREG', 'TV9999B12M4', 'CVKR', 'MT_QUE');
    INSERT INTO TEST_RUA_MER VALUES ('Bond', 'BREG', 'LV9999B12F6', 'OPTQ', 'BWQT_UI');

Expected output in table TEST_RUA_MER:

CLASS   ID_LL   ID_UU       TKR     NAME
Bond    BREG    TV9999B12M4 CVKR    FRTG_OP
Bond    BREG    SQTUREGBFNO LRQU    BEGT_TU

Popeye Popeye · Accepted Answer · 2021-01-19T10:29:51

You can use the MERGE statement as follows:

merge into TEST_RUA_MER trg
using TEST_RUA src
on (trg.ID_LL = src.ID_LL and trg.ID_UU = src.ID_UU and trg.TKR = src.TKR)
when matched then 
     update set trg.name = src.name
when not matched then 
     insert values (src.class, src.ID_LL, src.ID_UU, src.TKR, src.name)

db<>fiddle

Delta load logic using oracle merge sql statement

1 Answers