Ask the Expert

Transferring data from four tables to another four tables

I have four tables, each contain a lakh of records. I need to transfer the records from those four tables to another four tables.

The first table is the primary table. The second and fourth tables are its secondary tables. The third table is a secondary table to the second table. Should I do it using a PL/SQL procedure?

I used four cursors for each and populate the data, but it was very slow. I want to increase the performance. How to do it?

    Requires Free Membership to View

I have one simple mantra that really helps when it comes to performance of database operations. It goes as follows:

  1. If it can be done with straight SQL statements, do it with SQL.
  2. If it can't be done with straight SQL, then use PL/SQL.
  3. If it can't be done with PL/SQL, then use Java in the database.
  4. If it can't be done with Java in the database, then use a C++ external procedure or something outside of the database.
As with any rules of thumb, there are exceptions. But in your case, rule number 1 definitely applies! Why use PL/SQL when simple SQL commands will do the job? Assume you want to copy data from tables TABLE1, TABLE2, TABLE3, and TABLE4 to new tables NEW_TABLE1, NEW_TABLE2, NEW_TABLE3, and NEW_TABLE4. Then, the following commands will help you: INSERT INTO new_table1 SELECT * FROM table1; INSERT INTO new_table2 SELECT * FROM table2; INSERT INTO new_table3 SELECT * FROM table3; INSERT INTO new_table4 SELECT * FROM table4; COMMIT; If you are worried about running out of undo space, then put a COMMIT between each of the INSERT statements. Then, since your case indicates that there are referential integrity constraints, add identical integrity constraints to the new tables after you have them populated.

For More Information

This was first published in March 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: