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 using cursor in PL/SQL

    Hi there,

    can someone explain me how to use a cursor to retrieve all the rows in one table and then insert some of the records into another table? (PL/SQL) Or maybe tell me where I can find a good tutorial or information about how to do it?

    Thanx a lot, Fausto

    The real problem is the following:

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Try something like this:

    Code:
    
    begin
      for r in (select * from specs)
      loop
        insert into specs_info
             values (r.id,1,r.inp1,r.out1);
        insert into specs_info
             values (r.id,2,r.inp2,r.out2);
        insert into specs_info
             values (r.id,3.r.inp3,r.out3);
      end loop;
    end;
    /
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2003
    Location
    Frankfurt - Germany
    Posts
    7
    Thanx a lot. It works fine :-)

Posting Permissions

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