Need a Bulk Insert Query

I need to insert data from one table into another which have same columns. How do I do that with a Bulk Insert? Please suggest.
temp_table (102 columns) into prov_db_table (102 columns).
srikanthradixAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
insert into prov_db_table
select * from temp_table

(assuming the columns are in the same order)
0
jamesguCommented:


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
0
srikanthradixAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Terry WoodsIT GuruCommented:
Hmm, ok. When I've done VERY large insert queries, I've increased the performance by turning off the indexes and triggers on the table being inserted into. In an informix database, I've also turned off transaction logging on the database instance.

I think that's about the limit of my knowledge on any alternatives. How many rows do you have to insert?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamesguCommented:
TerryAtOpus is right, you can import the data and then create all indexes and triggers

you may commit the transaction for a limit # of records to keep transaction size low

and you can use the append hint as well

insert /*+ append */ into prov_db_table
select * from temp_table
0
srikanthradixAuthor Commented:
@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?
0
srikanthradixAuthor Commented:
@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?
0
SujithData ArchitectCommented:
If you are looking for the fastest solution:
insert /*+ APPEND */  is the way to go.
0
jamesguCommented:
commit for a limit # of records to keep the transaction size low?

try to find out some column(s) to subset the data from temp_table,say, the table has a record_no which is unique
(create an index on it)

do

insert /*+ append */ into prov_db_table
select * from temp_table where record_no <=100000;
commit;

insert /*+ append */ into prov_db_table
select * from temp_table where record_no <=200000;
commit;
...
0
srikanthradixAuthor Commented:
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?
0
srikanthradixAuthor Commented:
And also in my Production DB, there are 5 billion rows, Does rebuilding indexes affect performance?
0
srikanthradixAuthor Commented:
or /* +APPEND */ for that matter. Does it affect any performance?
0
srikanthradixAuthor Commented:
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;



0
srikanthradixAuthor Commented:
/*+ 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?
0
jamesguCommented:
0
srikanthradixAuthor Commented:
Thanks, that explains a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.