CODEWITHSUNDEEP
sqlsql-server
Michał Szymański
Michał Szymański

Reputation: 354

How do I insert into table primary keys from two other tables making every combination?

I use MS SQL Server and I have 3 tables, A, B and C. Both A and B have column called Id. I would like to insert into C those values making every combination, so lets say table A has data:

Id
1
2
3

Table B has data:

Id
4
5

And I am trying to achive that table C has data:

Id | A_Id| B_Id
1  |1    | 4
2  |1    | 5
3  |2    | 4
4  |2    | 5
5  |3    | 4
6  |3    | 5

I will greatly appreciate any help.

Upvotes: 2

Views: 239

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need cross join :

select *
from tablea a cross join
     tableb b
order by a.id;

So, you can use insert statement :

insert into tablec (A_Id, B_Id)
     select *
     from tablea a cross join
          tableb b
     order by a.id;

Upvotes: 6

Related Questions