• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1556
  • Last Modified:

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).
0
srikanthradix
Asked:
srikanthradix
  • 9
  • 4
  • 2
  • +1
3 Solutions
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
sujith80Commented:
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 9
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now