Conditionally insert rows from Staging table to main table in oracle

Question

Database I'm using is Oracle Database 19C Enterprise edition I am struggling to find a solution to an issue where I need to insert entries into a my main table based on condition from the staging table.

There are 3 scenarios.

Staging table sample 1:

pool_name	account	flag
pool-b	acc1	S
pool-b	acc1	I
pool-n	acc2	S
pool-n	acc2	N

In the above scenario I need to read this table and insert to a main table only 3 entries, the condition is if pool_name ends with -b exist when the flag is 'S' this entry need to insert and the entry with pool_name which ends with -n and flag is 'S' needs to ignore. Where the flag is 'I' and 'N' always needs to be inserted.

Scenario 2

pool_name	account	flag
pool-b	acc1	I
pool-n	acc2	S
pool-n	acc2	N

When pool_name ends with -b and flag 'S' does not exists in staging table, pool_name ends with -n and flag 'S' needs to be entered. The tricky part is , if in the main table pool_name which ends with -b and flag 'S' exist then the insert of the pool-n with flag 'S' should not be performed.

Scenario 2

pool_name	account	flag
pool-b	acc1	I
pool-b	acc2	S
pool-n	acc2	N

When pool_name ends with -n and flag 'S' does not exists in staging table, pool_name ends with -b and flag 'S' needs to be entered. The tricky part here is , if in the main table pool_name which ends with -n and flag 'S' exist then the in the main table this record should be override by the pool-b with hte flag 'S'

I understand this is bit of abstract representation. But if anyone can help me on this that will be greatly appreciated.

I have tried to split the -b and -s and populate in different columns in staging table and try to use that to identify those two entries by using group by, those this approach provided me with a single entry it does not support the logic I mentioned above.

Answer 1

I think you can use:

INSERT INTO main_table (pool_name, account, flag)
  SELECT pool_name, account, flag
  FROM   staging_table s
  WHERE  flag != 'S'
  OR     (   flag = 'S'
         AND (   pool_name LIKE '%-b'
             OR  (   pool_name LIKE '%-n'
                 AND NOT EXISTS(SELECT 1
                                FROM   staging_table
                                WHERE  flag = 'S'
                                AND    pool_name LIKE '%-b')
                 AND NOT EXISTS(SELECT 1
                                FROM   main_table
                                WHERE  flag = 'S'
                                AND    pool_name LIKE '%-b')
                 )
             )
         );

DELETE FROM main_table
WHERE flag = 'S'
AND   pool_name LIKE '%-n'
AND   EXISTS(SELECT 1
             FROM   main_table
             WHERE  flag = 'S'
             AND    pool_name LIKE '%-b');

Answer 2

Try it like here:

--  creating tables to work with:
Create Table tbl_stage (POOL_NAME VarChar2(12), ACCOUNT VarChar2(12), FLAG VarChar2(1));
--  Table TBL_STAGE created.
Create Table tbl_main (POOL_NAME VarChar2(12),  ACCOUNT VarChar2(12), FLAG VarChar2(1));
--  Table TBL_MAIN created.

-- -------------- Sample *1* ------------------------
Insert Into tbl_stage (POOL_NAME, ACCOUNT, FLAG)
        ( Select 'pool-b', 'acc1', 'S' From Dual Union All
          Select 'pool-b', 'acc1', 'I' From Dual Union All
          Select 'pool-n', 'acc2', 'S' From Dual Union All
          Select 'pool-n', 'acc2', 'N' From Dual 
        );
--  4 rows inserted.

INSERT INTO tbl_main
Select      POOL_NAME,  ACCOUNT, FLAG
From          tbl_stage
Where     SubStr(POOL_NAME, -2) || FLAG != '-nS';
--  3 rows inserted.

Select * From tbl_main;
/*  R e s u l t :
POOL_NAME    ACCOUNT      F
------------ ------------ -
pool-b       acc1         S  *1*
pool-b       acc1         I  *1*
pool-n       acc2         N  *1*   */

-- -------------- Sample *2* ------------------------
Delete tbl_stage;
--  4 rows deleted.
Insert Into tbl_stage (POOL_NAME, ACCOUNT, FLAG)
        ( Select 'pool-b', 'acc1', 'I' From Dual Union All
          Select 'pool-n', 'acc2', 'S' From Dual Union All
          Select 'pool-n', 'acc2', 'N' From Dual 
        );
--  3 rows inserted.

INSERT INTO tbl_main
Select    s.POOL_NAME, s.ACCOUNT, s.FLAG 
From      tbl_stage s
Where     SubStr(s.POOL_NAME, -2) || s.FLAG != '-bS' And
          SubStr(s.POOL_NAME, -2) || s.FLAG != 
          Case When EXISTS( Select 1 From tbl_main Where SubStr(POOL_NAME, -2) || FLAG = '-bS' )
               Then '-nS' 
          Else 'xXx'
          End;
--  2 rows inserted.

Select * From tbl_main;
/*  R e s u l t :
POOL_NAME    ACCOUNT      F
------------ ------------ -
pool-b       acc1         S  *1*
pool-b       acc1         I  *1*
pool-n       acc2         N  *1*
pool-b       acc1         I  *2*
pool-n       acc2         N  *2*   */

-- -------------- Sample *3* ------------------------
Delete tbl_stage;
--  3 rows deleted.
Insert Into tbl_stage (POOL_NAME, ACCOUNT, FLAG)
        ( Select 'pool-b', 'acc1', 'I' From Dual Union All
          Select 'pool-b', 'acc2', 'S' From Dual Union All
          Select 'pool-n', 'acc2', 'N' From Dual 
        );
--  3 rows inserted.

MERGE INTO tbl_main t
USING
  ( Select    s.POOL_NAME, s.ACCOUNT, s.FLAG, 
              Case When EXISTS(Select 1 From tbl_main Where SubStr(POOL_NAME, -2) || FLAG = '-nS' )
                   Then 'UPD'
              Else 'INS' 
              End "ACTION"
    From      tbl_stage s
    Where     SubStr(s.POOL_NAME, -2) || s.FLAG = '-bS' And
              NOT EXISTS ( Select 1 From tbl_stage Where SubStr(s.POOL_NAME, -2) || s.FLAG = '-nS' )
  ) x ON (x.ACTION = 'UPD')
WHEN NOT MATCHED THEN
        INSERT (t.POOL_NAME, t.ACCOUNT, t.FLAG)
        VALUES (x.POOL_NAME, x.ACCOUNT, x.FLAG)
WHEN MATCHED THEN
        UPDATE SET t.POOL_NAME = REPLACE(t.POOL_NAME, 
                                         CASE  WHEN SUBSTR(t.POOL_NAME, -2) || t.FLAG = '-nS' THEN '-n' END, 
                                         CASE  WHEN SUBSTR(t.POOL_NAME, -2) || t.FLAG = '-nS' THEN '-b' END)
-- 1 row merged.

Select * From tbl_main;
/*  R e s u l t :
POOL_NAME    ACCOUNT      F
------------ ------------ -
pool-b       acc1         S  *1*
pool-b       acc1         I  *1*
pool-n       acc2         N  *1*
pool-b       acc1         I  *2*
pool-n       acc2         N  *2*
pool-b       acc2         S  *3*    */

NOTE: The last action was INSert as there are no rows in tbl_main with pool -n and flag S. If there was such a row (or rows) it (they) would be UPDated with -b (where -n in POOL_NAME column an S in FLAG column). All of the above samples does not take care of account (though it is suspicious) cause you don't mention accounts in your description of the issues.