Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: Inserting data into Table

    Hi There,

    I've a situation, where in I've to read a record which will have a primary key column and some attributes. I've to read this record and then insert the data into another table. The data should go like one record for, one attribute and primary key combination.

    For example, this is my record:

    Primary_column attribute1 attribute2 attribute3 attribute4

    Now I've to insert this record into another table, like this

    Insert into TEST values (Primary_column, attribute1)
    Insert into TEST values (Primary_column, attribute2)
    Insert into TEST values (Primary_column, attribute3)
    Insert into TEST values (Primary_column, attribute4)


    Is there a way, so that I can read the parent record column by column and at the same time I can enter a record into my target table.

    Your suggestions are appreciated.

    Thanks,
    Gopi.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could select column by column in separate SELECT statements and create their UNION; something like this Scott's schema based example:
    Code:
    SQL> CREATE TABLE test (pk_col NUMBER, col VARCHAR2(20));
    
    Table created.
    
    SQL> INSERT INTO test (pk_col, col)
      2    (SELECT deptno, dname FROM DEPT WHERE deptno = 10
      3     UNION
      4     SELECT deptno, loc FROM DEPT WHERE deptno = 10
      5    );
    
    2 rows created.
    
    SQL> SELECT * FROM test;
    
        PK_COL COL
    ---------- --------------------
            10 ACCOUNTING
            10 New York
    
    SQL>

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    Thanks for your suggestion, is there a way to use array kind of thing where in i can store all the values in the array and loop through it to populate my target table.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Another way would be unpivotting,something like:
    Code:
    SELECT a.primary column,
           CASE WHEN x.attr=1 THEN a.attr1
                WHEN x.attr=2 THEN a.attr2
                 ...
           END attribute
    FROM attr_table a CROSS JOIN (any table having attr column with values 1..N) x
    where N = number of attributes in TEST. You may use any kind of row generator to get the content of the X table.

    However, before storing data in a table like TEST, I would read this thread on AskTom: http://asktom.oracle.com/pls/asktom/...10678084117056 and resume whether I really want it.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Which Oracle version (to 4 decimal places) are you (ab)using?

    >is there a way to use array kind of thing where in i can store all the values in the array and loop through it to populate my target table.
    ARRAY? LOOP? Neither explicitly exists in regular SQL; which you original post implied.
    ARRAY & LOOP could be done in PL/SQL.


    Keep in mind, that rows in a table have NO inherent order.
    So why do I mention this?
    Keep reading.
    Code:
    Insert into TEST values (Primary_column, attribute4);
    Insert into TEST values (Primary_column, attribute2);
    Insert into TEST values (Primary_column, attribute1);
    Insert into TEST values (Primary_column, attribute3);
    SQL> SELECT * FROM test;
    
        PK_COL COL
    ---------- --------------------
            10    B
            10    N
            10    Z
            10    Q
    Which COL value corresponds which original attribute?
    Keep in mind that rows in a table have NO inherent order.
    The order in which rows are inserted to NOT have to match the order in which they are returned.

    Even if you can implement original "requirements", I don't see where it could be used in the real world; besides be an arbitrary homework assignment.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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