Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Bulk insert into global temporary table

Hi ,


create a global temporary table mytable as follows in Oracle:

create global temporary table mytable (

   column definitions

) on commit preserve rows;

However, I also want to bulk insert hundreds of rows without writing a SQL insert statement hundreds of time.

select * from mysourcePool;  -- hundreds of rows to be used in bulk insert data

How do I combine both operations?
0
LuckyLucks
Asked:
LuckyLucks
2 Solutions
 
sdstuberCommented:
insert into mytable (a,b,c,d)  select w,x,y,z from mysourcepool.


replace a,b,c,d,w,x,y,z  with your real columns, expand as needed
0
 
Mark GeerlingsDatabase AdministratorCommented:
You don't need hundreds of "insert...select ...from..." statements to insert hundreds of rows to either a global temporary table or a standard Oracle table.

You simply add "where" clause conditions to the "select ... from..." part of the statement to include whatever rows should be included.   These will all be inserted in one step with no "bulk collect" needed.  This is faster and more efficient that using a PL\SQL procedure that includes a cursor and "bulk collect".
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now