possible to insert select query results?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: possible to insert select query results?

  1. #1
    Join Date
    Feb 2006
    Posts
    5

    possible to insert select query results?

    hi,

    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 2002
    Location
    Colorado Springs
    Posts
    5,253
    how do you define the association between the two tables? What makes a value of emp1_id match a particular value of emp2_id?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2006
    Posts
    5
    Please ignore emp1_emp2 as an association table, its irrelevant here. Lets say I just want to insert pk of emp1 and pk of emp2, is there any way to do that?


    Quote Originally Posted by slimdave
    how do you define the association between the two tables? What makes a value of emp1_id match a particular value of emp2_id?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Well you could ...

    Code:
    insert into t1
    select col1 from t2;
    
    insert into t1
    select col1 from t2
    union all
    select col1 from t3;
    ... that sort of thing
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2006
    Posts
    5
    Thanks.

    But the following will only insert col1 of t2 and col1 of t3 both into
    one column of t1. If t1 has two columns: col1 and col2, I want to insert col1 of t2 into col1 of t1, and insert col1 of t3 into col2 of t2,
    how can we do that?

    Quote Originally Posted by slimdave
    Well you could ...

    Code:
    insert into t1
    select col1 from t2;
    
    insert into t1
    select col1 from t2
    union all
    select col1 from t3;
    ... that sort of thing

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You have to have some definition of what value from t3 goes into the same row as a value from t2 or the whole exercise is meaningless.

    Code:
    insert into t1 (col1, col2)
    select t2.col3, t3.col4
    from t2, t3
    where ... ??? ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2006
    Posts
    5
    Thanks, that should be it.

    Quote Originally Posted by slimdave
    You have to have some definition of what value from t3 goes into the same row as a value from t2 or the whole exercise is meaningless.

    Code:
    insert into t1 (col1, col2)
    select t2.col3, t3.col4
    from t2, t3
    where ... ??? ...

Posting Permissions

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


Click Here to Expand Forum to Full Width