Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: possible to insert select query results?

    I am wondering if I can insert the result of a select query from
    one table into another table. I have 3 tables: emp1, emp2, emp1_emp2, I want to insert pks of emp1 and emp2 into their association table emp1_emp2. If I can insert a row into emp1 and emp2 first, then insert their pks into their association table, I can save a second hit to db. Can I achieve something similar to this (I know the following won't work)?:

    insert into emp1_emp2 values (select emp1_id from emp1, select from emp2_id from emp2);

    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    Thanks for the info. I usually use DB2, I am a new Oracle9i user, just need a quick hint. I know I can use update to have workaround, cannot find any better solution for this. Just wondering if there is any way to do this which cannot be done in DB2. Thanks.


    Quote Originally Posted by anacedent

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What you are saying is that you are unwilling or incapable to RTFM when even provided a URL which contains the answer to your question.

    You're On Your Own (YOYO)!
    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.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd like to ask something, which is unclear to me: you have two tables, "emp1" and "emp2". Third table, "emp1_emp2" should store emp1's and emp2's primary key column values. You call that table an "association" table. What does it mean? How are primary keys associated (if they are)? Should they be stored in pairs? Can you show a little example? Because I somehow doubt that this is the solution you need:

    INSERT INTO emp1_emp2
    SELECT e1.primary_key_column, NULL FROM emp1 e1
    UNION
    SELECT NULL, e2.primary_key_column FROM emp2 e2;

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    An association table is (usually) a table that is used to make a parent-child relationship between two tables. Regullary, for every row in parent there must be at least one relationship to a child (one to many). It is unclear as of now how are your tables "associated".

    You could save the _additional hit_ if you use INSERT ALL, for example
    Code:
    SQL> create table t1 (
      2     id      number primary key
      3  )
      4  /
    
    Table created.
    
    SQL> create table t2 (
      2     id      number primary key
      3  )
      4  /
    
    Table created.
    
    SQL> create table t1_t2 (
      2     t1_id   number,
      3     t2_id   number,
      4     constraint fk_t1 foreign key ( t1_id ) references t1,
      5     constraint fk_t2 foreign key ( t2_id ) references t2
      6  )
      7  /
    
    Table created.
    
    SQL>
    SQL> REM This is to test the effect of what would happen if you try
    SQL> REM to insert a row into the association table with non-existant
    SQL> REM ids on both, t1 and t2.
    SQL>
    SQL> insert into t1_t2 values ( 1, 1 );
    insert into t1_t2 values ( 1, 1 )
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (A.FK_T2) violated - parent key not found
    
    
    SQL> insert all
      2     into t1 values ( t1_id )
      3     into t2 values ( t2_id )
      4     into t1_t2 values ( t1_id, t2_id )
      5  select 1 t1_id, 2 t2_id from dual
      6  /
    
    3 rows created.
    
    SQL>
    SQL> select * from t1_t2;
    
         T1_ID      T2_ID
    ---------- ----------
             1          2
    
    SQL> select * from t1;
    
            ID
    ----------
             1
    
    SQL> select * from t2;
    
            ID
    ----------
             2
    
    SQL>
    ..but watch out, don't change the tables order in the INSERT ALL!!
    Code:
    SQL> insert all
      2     into t1_t2 values ( t1_id, t2_id )
      3     into t1 values ( t1_id )
      4     into t2 values ( t2_id )
      5  select 3 t1_id, 4 t2_id from dual;
    insert all
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (A.FK_T2) violated - parent key not found
    
    
    SQL>
    You gotta make sure you insert into your base tables FIRST, and then you insert into your association table later.
    Last edited by JMartinez; 02-22-06 at 09:05.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by rick_cold
    Thanks for the info. I usually use DB2, I am a new Oracle9i user, just need a quick hint. I know I can use update to have workaround, cannot find any better solution for this. Just wondering if there is any way to do this which cannot be done in DB2. Thanks.
    insert into mytable (col1,col2,col3)
    select x,y,z
    from my_other_table
    where q=23;


    The number and type of the columns from the select MUST match the columns in the table that you are inserting into. Welcome to the wonderful world of oracle.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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