Solved

ORACLE - PL/SQL traverse a set of data, to insert the data into another table

Posted on 2011-02-14
5
649 Views
Last Modified: 2012-05-11
Good afternoon, I have a PRODUCTS table in oracle with more than 10000 records. In a new design this table has been divided into 6 tables.
What I need is to get the data from the PRODUCTS table and insert new records in these 5 tables.
 I understand that I do a select on this table with some filters in the where, my question is, as it runs a data set in oracle to insert each row in the corresponding table
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 34891822
10,000 isn't a lot of data.  Can you do a simple: insert into new_table1 (select columns from old_table);

for each of the new tables?
0
 

Author Comment

by:enrique_aeo
ID: 34891904
hahaha, I sincerely hope that is so, you are awesome, I look a little more the subject and I confirm. Thanks
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 334 total points
ID: 34891916
You could use either one (or multiple)  PL\SQL procedures (or anonymous PL\SQL blocks) for this task, or you could (more efficiently) simply use separate SQL insert commands for each of these new tables.

The SQL insert statements could look like this:

insert into new_table1
select * from old_table
where [conditions for new_table1]

insert into new_table2
select * from old_table
where [conditions for new_table2]

etc.

If you are concered with performance, you can make those inserts significantly faster by first setting each of the new tables to "nologging" like this:
alter table [new_table1] nologging;
alter table [new_table2] nologging;
etc.

Then change your insert statements to look like this:

insert /*+APPEND */ into new_table1
select * from old_table
where [conditions for new_table1]

insert /*+APPEND */ into new_table2
select * from old_table
where [conditions for new_table2]

If you use the "nologging" option, you will want to take a backup of the new tables after you load them.
0
 

Author Comment

by:enrique_aeo
ID: 34891947
ok, I know why I do it row by row, for example in the table is a column coduser products, but in the new table name to be written, in order to do this I have a function in which you step and I returned coduser name
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 334 total points
ID: 34892100
OK, but you could call that function either inside a PL\SQL procedure (or anonymouse block) as you process each row, or the SQL insert statements could call this function for each row as well.  The syntax for that would look more like this:

insert into new_table1
 (column1, column2, column3, etc.)
select column1, column2, function (parameters...), etc.

Remember, whatever can be done with SQL statements wil be more efficient than using those same SQL statements inside PL\SQL.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question