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

    Unanswered: Sql Copy/insert Statement

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    16

    Lightbulb One more table I think

    You've got the right idea.

    I think you need two tables for all the relationships you'll want though, if the number if ins and out are ever different for example. And you'll probably want to record the type of information that will be sent in and returned...

    SPEC_IN(ID,SEQ,NAME,TYPE)
    SPEC_OUT(ID,SEQ,NAME,TYPE)

    -Chris

  3. #3
    Join Date
    Oct 2003
    Location
    Frankfurt - Germany
    Posts
    7
    Hi Chris, and thanx for your posting.

    My main problem is how to copy the rows from the original table to the new table(s). Can you help me there also?

    Thanx, Fausto

  4. #4
    Join Date
    Oct 2003
    Posts
    16

    Lightbulb yah sure...

    you'll want 6 queries ( i assume )

    insert into SPEC_IN (id,seq,name)
    VALUES ( SELECT id,1,INP1 FROM SPECS );
    insert into SPEC_IN (id,seq,name)
    VALUES ( SELECT id,2,INP2 FROM SPECS );
    insert into SPEC_IN (id,seq,name)
    VALUES ( SELECT id,3,INP3 FROM SPECS );

    And the same for the spec_out table

    -Chris
    http://www.bitesizeinc.net/index.php/sql.html

  5. #5
    Join Date
    Oct 2003
    Location
    Frankfurt - Germany
    Posts
    7
    Unfortunatelly it does not work. I get the following error message:

    ORA-00936: missing expression

    But I managed to do what I needed using PL/SQL.

    Thanx anyway for you help.

    Fausto

  6. #6
    Join Date
    May 2003
    Posts
    87

    Re: yah sure...

    The correct syntax is :

    insert into SPEC_IN (id,seq,name)
    SELECT id,1,INP1 FROM SPECS;

    Originally posted by christodd
    you'll want 6 queries ( i assume )

    insert into SPEC_IN (id,seq,name)
    VALUES ( SELECT id,1,INP1 FROM SPECS );
    insert into SPEC_IN (id,seq,name)
    VALUES ( SELECT id,2,INP2 FROM SPECS );
    insert into SPEC_IN (id,seq,name)
    VALUES ( SELECT id,3,INP3 FROM SPECS );

    And the same for the spec_out table

    -Chris
    http://www.bitesizeinc.net/index.php/sql.html

Posting Permissions

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