[RESOLVED] Insert into from 2 tables

Hey,

I got 2 tables each with their own key

Table 1
Key1

Table2
Key2

I need a SQL statement that will insert into Table3 Key1 as primary and Key2 as foreign.

What would be the best way to do that?

Table3 end result would look like:

Key1 | Key2
1 | 1
2 | 1
3 | 1
1 | 2
2 | 2
3 | 2

Thanks for any direction.

I don't think you can do what you just described. If you want to insert Key1 as a primary key you can't insert that key twice in your example 1,1 and 1,2. If you want you can create a combination PK based on Key1 and Key2 and have both columns FKed to their respective tables...... Or add a third column as the PK and then the other two as columns with FKs to the repective tables.

Is it that you want every row in table1 to relate to every row in table 2?

Primary keys can't have duplicate values. So the Table3 result you posted won't be possible if Key1 is declared as Primary Key in Table3.

Looking from the result you posted, I assume you want the Cartesian Product (cross join) of Key1 (from Table1) and Key2 (from Table2). In that case this query should work for you:

Code:

---

INSERT INTO Table3 (Key1, Key2)
SELECT Table1.Key1, Table2.Key2 From Table1, Table2
ORDER BY Table2.Key2, Table1.Key1

---

Pradeep :)

Thats exactly what I needed Pradeep. Thank both of you for your time.