Best practice for table to table operation

best practicesoracle

I have a table loaded daily (let's call it A_table), with the data from this table I have to load another table (B_table).

A is replaced every day (truncated and loaded anew), B is the final table, which have to stay and it'll be queried.
As far as I know we should have a relatively low number number of row loaded daily in A while B is ever-expanding.
I have to do insert or update (from A to B) based on what I already have on information in B.

I'm pretty new to this, but I think it's not an uncommon operation. Is there a best practice or more simply a common approach to this type of operation ?

Best Answer

This is what the MERGE statement was created for:

merge into table_b 
using
(
  select pk_col, col1, col2
  from table_a
) t on (t.pk_col = table_b.pk_col)
when matched then 
  update
    set col1 = t.col1, 
        col2 = t.col2
when not matched then 
  insert (pk_col, col1, col2)
  values (t.pk_col, t.col1, t.col2);
Related Question