oracle bulk insert

Question

I am not familiar with PLSQL , however I have to perform a bulk insert for a task.

Basically I have to query table one to get one column and then use it on a different table to insert it. Something like this:

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 around 4k+ ids in the file and each id would have different range of distinct col1: Max:165 million, min ~2k

I am trying to achieve this "Read from one table and insert into other using bulk insert", it is okay if this runs overnight etc.

I got this script from some research online :

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 might be useful in my case, but I dont quite understand the semantics. Where do I mention column1 ...also for insert values are expressed as ObjectTable$(x) insetead of values(..,..,..) .

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

The DB is 10g

Thanks !

Answer 1

You do not need the bulk collect at all. Heck, you don't even need PL/SQL - SQL can do the bulk insert as well!

simply 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);