0

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 ?

  • If cardinality of A is low, then the solution using MERGE proposed below is probably optimal. You must test it in your own environment (and please ensure there is an index on pk or columns used in the ON clause). – Raj Mar 9 '17 at 20:32
  • Then I should go back to the documentation because I'm missing something about the MERGE. – Jackyz Mar 10 '17 at 8:23
2

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);
  • I'm going back to the MERGE and try to apply it to my problem. If I can work with it I'll accept your answer, else I'll try to expand my question with more detail. – Jackyz Mar 10 '17 at 8:25
0

My solution, and since I usually overcomplicate stuff so I'm not that sure about it, is to create a cursor on table_A and go row by row. I'm still kinda dubious on exactly how.

  • 2
    I guess that was meant to be an edit to your question. – mustaccio Mar 9 '17 at 18:02
  • no, that was meant to be an answer, albeit not a explicative one, since it is an option on how to resolve my problem. – Jackyz Mar 10 '17 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.