CODEWITHSUNDEEP
sqlsql-servert-sqlinsert
MMeirelles
MMeirelles

Reputation: 83

SQL Server - Insert into a combination of two tables

I need to combinate two tables in SQL Server. But I need to have a row with each item of table A with each item of table B, resulting in a table C. I would be like this:

Table A
A
B
C
D

Table B
1
2
3

Table C
column x | cloumn Y
   A           1
   A           2
   A           3
   B           1
   B           2 
   B           3
   C           1
   C           2
   C           3
   D           1
   D           2
   D           3

Thanks all!

Upvotes: 2

Views: 2527

Answers (2)

Metaphor
Metaphor

Reputation: 6395

Looks like you're trying to get the following:

select a.col1, b.col1
into tableC
from tableA a
cross join tableB b

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

You can simply do this:

SELECT 
 a.a + CAST(b AS VARCHAR(2)) AS a
FROM tablea a
CROSS JOIN tableb AS b;

See it in action:

Then you can use the INTO clause to insert them into a table already exists:

INSERT INTO tablec(c)
SELECT 
 a.a + CAST( b AS VARCHAR(2)) AS a
FROM tablea a
CROSS JOIN tableb AS b;

or create a new table from these values:

SELECT 
 a.a + CAST( b AS VARCHAR(2)) AS c
INTO Tablec
FROM tablea a
CROSS JOIN tableb AS b;

Updated SQL Fiddle Demo

Note that: I assumed the columns' names, since you didn't specify them in your question.

Upvotes: 6

Related Questions