Need a Bulk Insert Query

insert into prov_db_table
select * from temp_table

(assuming the columns are in the same order)

if possible,  you can use  the below statement to create the  temp table which will have the same schema as prov_db_table,

create table temp_table as select * from prov_db_table where 1 = 2;

if the tables already exists, and two tables have the same schema, use the one TerryAtOpus provided,
otherwise, you have to list all columns

I mean, the tables already exist.

I don't need the insert  query like
insert into prov_db_table select * from temp_table.

But I need a BULK COLLECT or something, which is faster than ordinary insert query. How can i do that?

Please suggest one that is faster than traditional insert query.

@TerryAtOpus
I have to insert 3 million rows and there are 102 columns.
@jamesqu
Can you please explain how can i do the transaction commit for a limit # of records to keep the transaction size low?
Can you please explain me how to use the append hint?

@jamesqu
I have read about append hint and it says
"By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table". What does it mean?

If you are looking for the fastest solution:
insert /*+ APPEND */  is the way to go.

How about doing this?

alter index .... unusable;
alter session set skip_unusable_indexes=true;
insert ....
alter index ... rebuild.

Will this affect anything? Is this safe? Even if I can do this, Is this best practice to do so?

And also in my Production DB, there are 5 billion rows, Does rebuilding indexes affect performance?

or /* +APPEND */ for that matter. Does it affect any performance?

How about using BULK COLLECT WITH LIMIT and FORALL like this? Will this be faster?

DECLARE

CURSOR temp_cur
IS
SELECT * FROM temp_table;

TYPE data_temp
      IS TABLE OF temp_cur%ROWTYPE;

bulk_data data_temp;

...
..
.
BEGIN
   OPEN temp_cur;
    LOOP
        FETCH temp_cur BULK COLLECT INTO bulk_data
                   FROM temp_table LIMIT 1000;
                   
        EXIT WHEN
      bulk_data.COUNT = 0;

        FORALL i IN 1..bulk_data.COUNT
             INSERT INTO my_table VALUES bulk_data(i);
       COMMIT;

    END LOOP;
   CLOSE temp_cur;
     
   ...
END;

/*+ APPEND */ hint seems to be better bet. Can you please explain me What does an /*+ APPEND */ hint tells the Oracle and how does it differ from ordinary insert?

there are some some articles you can refer to

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1211797200346279484
http://www.oracle.com/technology/pub/articles/10gdba/week15_10gdba.html

I leave it you to do the reading

Thanks, that explains a lot.