Volumetric oracle insert

Question

I am not familiar with PLSQL, however I need to do a bulk insert for the task.

Basically, I need to query table 1 to get one column, and then use it in another table to insert it. Something like that:

for (ids in a file As cur_id) { Select DISTINCT column1 As col1_list from table1 where id=cur_id for (cols in col1_list as cur_col) Insert into table2 values ('cur_id','cur_col','214','234','first 3 chars of cur_col') } 

Now I have about 4k + ids in the file, and each identifier will have a different range of different col1: Max: 165 million, min ~ 2k

I am trying to achieve this: "Read from one table and paste into another using volumetric insertion", it is normal if this is done in one night, etc.

I got this script from some online research:

 CREATE OR REPLACE PROCEDURE test_proc IS TYPE TObjectTable IS TABLE OF ALL_OBJECTS%ROWTYPE; ObjectTable$ TObjectTable; BEGIN SELECT * BULK COLLECT INTO ObjectTable$ FROM ALL_OBJECTS; FORALL x in ObjectTable$.First..ObjectTable$.Last INSERT INTO t1 VALUES ObjectTable$(x) ; END; 

I think this may be useful in my case, but I do not quite understand the semantics. Where I mention column1 ... also for insertion values ​​are expressed as ObjectTable $ (x), inserting values ​​(.., .., ..).

Can someone explain the script to me and help me change it in my use case using the variables table1, table2, col1, ids etc, which I mentioned in my example.

DB is 10 g

Thanks!

Answer 1

You do not need to collect the mass. Heck, you don’t even need PL / SQL - SQL can do bulk insertion as well!

just create a view that matches the% rowtype of your target table

 create view v_Table1_Table2 as (select id, max(case when /* condition for column1 */ then /* expression for column1 */ else null; end) as column1, max(case when /* condition for column2 */ then /* expression for column2 */ else null; end) as column2, max(case when /* condition for column3 */ then /* expression for column3 */ else null; end) as column3 from table1 group by id ) 

then

 insert into table2 (select * from v_Table1_Table2 where id = :cur_id);