How to insert values from one table into another and then update the original table?

Question

Using Oracle, is there a way to insert values from one table into another table, then take an identity value from the inserted table and update a column in the original?

TABLE_1 is empty

ID    VALUE
-----------

Values from TABLE_2 ...

ID    VALUE
-----------
0     Val 1
0     Val 2
0     Val 3

...get inserted into TABLE_1 (with an Identity column)

ID    VALUE
-----------
1     Val 1
2     Val 2
3     Val 3

And then updates TABLE_2 with the IDs

ID    VALUE
-----------
1     Val 1
2     Val 2
3     Val 3

Answer 1

You need to get procedural for such a requirement. This solution uses SELECT ... FOR UPDATE which locks the source table, to prevent another session nabbing the record we want to give the new ID. It also gives us the WHERE CURRENT OF syntax, which makes it easy to identify the record we need to update.

This solution supposes the existence of a sequence for populating the identity column. There are other options available to us (including auto-increments in 12C) but the RETURNING clause is the key to snagging the new value.

declare
    cursor c2 is
        select * from table2
        for update of id;
    r2 c2%rowtype;
    new_id t1.id%type;
begin
    open c2;
    loop
        fetch c2 in r2;
        exit when c2%notfound;
        /* new record */
        insert into t1
        values (t1_seq.nextval, t2.value)
        returning t1.id into new_id;
        /* update existing record with id*/
        update t2
        set id = new_id
        where current of c2;
    end loop;
    commit;
end;
/

This solution is Row-By-Row" it is the easiest way to make sure that the new T1.ID gets applied to the correct row in T2. If T1 is small and/or this is a on-off exercise that's probably fine. But if performance is a concern there are tunings available.

Answer 2

If in table 2 are a lot of rows i recommend you to use bulk collect. It will help you to improve the performance on database. Like this:

declare 
type type_table2 is table of table2%rowtype index by binary_integer;
vt_table2 type_table2;
cursor cur_table2 is select * from table2;
begin
open cur_table2;
  loop
  fetch cur_table2 bulk collect into vt_table2 limit 500;
    for i in 1..vt_table2.count loop
      begin
        insert into table1
        values(i, vt.table2(i).value);
        update table2
        set id = i
        where current of cur_table2; 
      exception when other then
      excp := SQLERRM;
      dbms_output.put_line('Error:'||excp);
      end;
    end loop;
  exit when cur_table%notfound;
  end loop;
close cur_table2;
commit;
exception when other then
  excp := SQLERRM;
  dbms_output.put_line('Error:'||excp);
end;