Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Frankfurt - Germany
    Posts
    7

    Unanswered: Copy rows from a table to a new one

    Hi there,

    I am using Oracle 9i with TOAD for the first time and hope someone can give me an advice.
    My problem:

    I have a table SPECS with attributes (among others) ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 what means that the number of INP/OUT is restricted to a max of 3.

    In order to have as many INP/OUT as needed for each SPEC, I created a new table (SPECS_INFO) with attributes ID (foreign key from table SPECS) , SEQUENCE_NR, INPUT, OUTPUT where I want to copy the information from a row ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 (at the SPECS table) to three rows of the new SPECS_INFO table:

    For example:
    ID INP1 INP2 INP3 OUT1 OUT2 OUT3
    A1 CALL A CALL B CALL C ANSW A ANSW B ANSW C

    TO

    ID SEQUENCE_NR INPUT OUTPUT
    A1 1 CALL A ANSW A
    A1 2 CALL B ANSW B
    A1 3 CALL C ANSW C

    Can someone tell me what is the best way to do it or maybe suggest another solution for my problem?

    Thanks a lot,
    Fausto
    Last edited by fqueiroz; 10-17-03 at 11:22.

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    the best way is to use PL/SQL to do this. Use a cursor to retrieve all the rows in the SPECS table and using a loop insert the three(or more) records into the SPECS_INFO table. Be sure to commit frequently when u have a lot of rows.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Location
    Frankfurt - Germany
    Posts
    7
    Hi Edwin and thanx for your reply.

    I understood your idea, but as I am doing this for the first time, could you be a little more specific on how to use PL/SQL - cursor to do this?

    Thanx a lot,

    Fausto

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •