insert results of join in SQL server 2005

Question

I have two tables with more than 800 rows in each.Table names are 'education' and 'sanitation'.The column name 'ID' is common in both the tables.Now i want to join these both tables as full outer join and i want to save the results of this table as a new table.I can join it very easily,But how to save those data's as a new table.Please help Me.

select * into bc  from education e join sanitation s on e.id=s.id

I have around 30 columns in each table.So i can not explicitly create table schema for a new table.

I want all the columns from both tables.I have 20 tables with each 800 rows.From this 20 tables i want to make one master table taking 'ID' as primary key in all.

there is an example [here](http://msdn.microsoft.com/en-us/library/ms188029.aspx) see "Creating a table by specifying columns from multiple sources" — T I, Jul 31 '12 at 11:31
you could use `sys.columns` to construct (then execute) a dynamic query if you want to sidestep defining all columns and `*` — YS., Jul 31 '12 at 11:36
I want all the columns from both tables.I have 20 tables with each 800 rows.From this 20 tables i want to make one master table having 'ID' is common in all. — 7783, Jul 31 '12 at 11:45

score 1 · Answer 1 · answered Jul 31 '12 at 12:13

Sample Code:

Table one:

create table dummy1(
id int , fname varchar(50)
)
insert into dummy1 (id,fname) values (1,'aaa')
insert into dummy1 (id,fname) values (2,'bbb')
insert into dummy1 (id,fname) values (3,'ccc')
insert into dummy1 (id,fname) values (3,'ccc')

Table Two

create table dummy2(
id int , lname varchar(50)
)
insert into dummy2 (id,lname)  values (1,'abc')
insert into dummy2 (id,lname)  values (2,'pqr')
insert into dummy2 (id,lname)  values (3,'mno')

Now Create new table 3

create table dummy3(
id int , fname varchar(50),lname varchar(50)
)

Insert Query for table 3 look like

insert into dummy3 (id,fname,lname)
 (select a.id,a.fname,b.lname from dummy1  a inner join dummy2 b on a.id=b.id)

Table 3 will contain table1, table2 data

Yaqub Ahmad · Answer 2 · 2012-07-31T11:46:31.113

0

Follow below:

SELECT  t1.Column1, t2.Columnx
INTO    DestinationTable
FROm    education  t1
        INNER JOIN sanitation  t2 ON t1.Id = t2.Id

EDIT:

SELECT * will not work for you because you have a column ID which exists in both the tables. So the above solution will work you.

EDIT:

1- You can temporarily rename the Id column in one table, then try

2-  SELECT   *
    INTO    DestinationTable
    FROm    education  t1
            INNER JOIN sanitation  t2 ON t1.Id = t2.Id

3- Revert the column name back to Id.

edited Jul 31 '12 at 11:46

answered Jul 31 '12 at 11:32

Yaqub Ahmad

27,569
23
102
149

OP editted his question - "I have around 30 columns in each table.So i can not explicitly create table schema for a new table." – YS. Jul 31 '12 at 11:33

insert results of join in SQL server 2005

2 Answers2