possible to insert select query results?

[rick_cols]

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

[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?

[rick_cols]

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?

how do you define the association between the two tables? What makes a value of emp1_id match a particular value of emp2_id?

[slimdave]

Well you could ...

insert into t1
select col1 from t2;

insert into t1
select col1 from t2
union all
select col1 from t3;

... that sort of thing

[rick_cols]

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?

Well you could ...

insert into t1
select col1 from t2;

insert into t1
select col1 from t2
union all
select col1 from t3;

... that sort of thing

[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.

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

[rick_cols]

Thanks, that should be it.

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.

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