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 ?

Improve this question
2
  • 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
    Commented Mar 9, 2017 at 20:32
  • Then I should go back to the documentation because I'm missing something about the MERGE.
    – Jackyz
    Commented Mar 10, 2017 at 8:23

2 Answers 2

Reset to default
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);
Improve this answer
1
  • 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
    Commented Mar 10, 2017 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.

Improve this answer
2
  • 2
    I guess that was meant to be an edit to your question.
    – mustaccio
    Commented Mar 9, 2017 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
    Commented Mar 10, 2017 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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