Go Premium for a chance to win a PS4. Enter to Win

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

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

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
enrique_aeo
Asked:
enrique_aeo
  • 2
  • 2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
enrique_aeoAuthor Commented:
hahaha, I sincerely hope that is so, you are awesome, I look a little more the subject and I confirm. Thanks
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
enrique_aeoAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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