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

score 1 · Answer 1 · answered Jan 19 '21 at 10:29

1

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

answered Jan 19 '21 at 10:29

Popeye

35,427
4
10
31

thanks and how do we 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 – Symonds Jan 19 '21 at 10:33
That must be different sql statement as merge can handle only cases when data matched and not matched based on ON condition.and we are actually adding the data in TEST_RUA_MER table when it is not present in the TEST_RUA table. Now you want to delete it? – Popeye Jan 19 '21 at 10:35
yes i want separate delete statment for this ...i have update db fiddle and at the end after delete there should be only data in table TEST_RUA_MER which is updated and new data exist in table TEST_RUA.....https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b392828de0eef2e8cc30ae3838477f2a – Symonds Jan 19 '21 at 10:40
You can see in table TEST_RUA_MER there is row BREG SQTUREGBFNO LRQU which does not exist in TEST_RUA table and also row BOND BERF GV9999B12M1 TKXX TES_RES and row BOND BERF FV9999B12M3 BURR PRS_RES is equal in table TEST_RUA ....so i want to delete this row from table TEST_RUA_MER – Symonds Jan 19 '21 at 10:44
Use EXIST and NOT EXISTS. and yes, you should ask only one issue/doubt/question in one question to maintain the SO guidlines. Try it yourself and if you find issue then you can ask the question again on SO and yes, You can consider accepting this answer. – Popeye Jan 19 '21 at 11:02
the solution was not done..and you mentioned that in Merge three operations cannot perfrom but its possible...see https://dbfiddle.uk/?rdbms=oracle_18&fiddle=0c7141e216f5d129016c9bc1c0a14a41 ...Now i am facing new issue when i tried the merge with huge dataset i am getting errors as ORA-08006: specified row no longer exists ...i will post new question for this.. – Symonds Jan 20 '21 at 11:21
And Where I told you that it is not possible? Where I told you "in Merge three operations cannot perfrom"? You mentioned in your comment - "yes i want separate delete statment for this" and SO is not the code creation platform. It is very difficult to understand whole program of OP and answer according to their requirement. If you are stuck somewhere ask on SO. Don't ask for entire application coding. – Popeye Jan 20 '21 at 11:26

Delta load logic using oracle merge sql statement

1 Answers1