How to batch insert into two tables with one's id is another's foreign key?

user_1869908 0
Viewed 1575

There are two tables. Table User has columns as below

ID,NAME,AGE

And Table Asset has columns as below

ID,ID_USER,PRICE

Asset's ID_USER is the ID of User.

Table User's id uses sequece seq_t_user. Table Asset's id uses sequece seq_t_asset.

Now There are 1000000 users waiting to be inserted. We can use for loop to process every record seperately.

v_id_user = seq_t_user.nextval(); insert into User values(v_id_user, 'Lilie', 20); insert into Asset values(seq_t_asset.nextval(), v_id_user, 1000);

But this is very time consuming. Is there any way to batch insert into two tables at the same time?

oracle sql
2 Answers

The trick is to use INSERT ALL. This allows us to populate several tables from one SELECT source. My example uses dual so you will need to change that to suit your purposes:

insert all
into t_user (id, name, age) 
    values (seq_t_user.nextval, name, age)
into t_asset (id, id_user, price) 
    values (seq_t_asset.nextval, seq_t_user.nextval, price)
select 'ROBIN' as name
        , 23 as age
        , 1200 as price
from dual
union all
select  'APC' as name
         , 42 as age
         , 1100 as price
from dual
;

Here is a SQL Fiddle demo.

user_1463250

It looks like "unconditional multitable insert" is what you need.

I assumed you get all the values from my_table but you can modify as you need;

INSERT /*+ APPEND */ ALL
INTO USER (ID,NAME,AGE) 
    values (seq_t_user.nextval(), name, age)
INTO ASSET (ID,ID_USER,PRICE) 
    values (seq_t_asset.nextval(), seq_t_user.nextval(), price)
with MY_VALUES as
    (select /*+ parallel */ 
         name,
         age,
         price
     from my_table)
select /*+ parallel */ * from MY_VALUES

edit: We learned that we can't use sequence in multitable insert in the select statement. So we moved the sequence to the insert part.

user_57898960
Related