CODEWITHSUNDEEP
sqlsql-serversql-server-2008
njtd
njtd

Reputation: 115

SQL Insert data from 2 table to new table

I want to combine 2 table into 1 table as example below

Table1

    100
    200
    300

Table2

    A     B     C
    D     E     F
    G     H     I

OutputTable

   100   A     B     C
   100   D     E     F
   100   G     H     I
   200   A     B     C
   200   D     E     F
   200   G     H     I
   300   A     B     C
   300   D     E     F
   300   G     H     I

Thank you.

Upvotes: 0

Views: 59

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Use Cross Join which will create a Cartesian product of two tables

SELECT a.*,b.*
FROM   table1 a
       CROSS JOIN table2 b

or Use Cross Apply

SELECT a.*,b.*
FROM   table1 a
       CROSS Apply table2 b

Upvotes: 2

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

If you already have the new table then:

insert into new_table (field1,field2,field3,field4)
select 
     a.field1
     ,b.field2
     ,b.field3
     ,b.field4
from a
cross join b

If you do not have the table:

select 
    a.field1
    ,b.field2
    ,b.field3
    ,b.field4
into new_table
from a
cross join b

Upvotes: 3

Related Questions