# Best practice for table to table operation

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

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. Mar 10, 2017 at 8:25

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.

• I guess that was meant to be an edit to your question. 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. Mar 10, 2017 at 8:21