Creating a new table from two existing tables with the possibility of each combination

Question

I have two temporary tables #a and #b filled with integer values. Let them say that both of them contain 10 lines with values ​​of 1-10.

I want to create a third temporary table #c, which contains all possible combinations of a and b. Thus, it will have only 100 lines with (1,1), (1,2) ... (10, 10). How I will do it in SQL. The implementation I'm using is SQL Server 2012.

Answer 1

I think you can just do

SELECT * INTO #c FROM #a,#b 

Answer 2

Cross join will get all combinations

 SELECT a.Col , b.Col FROM TableA a CROSS JOIN TableB b 

Answer 3

 BEGIN DECLARE @a TABLE(x INT) DECLARE @b TABLE(x INT) INSERT INTO @a VALUES (1), (2) INSERT INTO @b VALUES (1), (2) select * from @a,@b END 

Answer 4

Here, of course, there are other correct answers, but I will add their best elements to fully answer the question:

 select a.Col as ColA -- Give the columns a name for the destination , b.Col as ColB into #c -- Generates destination temp table #c from #a as a cross join #b as b -- Cross join is the preferred syntax order by a.Col, b.Col -- Optional but often helpful (aesthetic, identities, indexing, etc) 

So, when you want a Cartesian product, use cross join .