join two tables to create a new table

[jadering]

Hi,

I have two tables which are linked via a field called linkField. Now I want to create a new table having all fields in these two existing tables. Since each of these two tables have hundurds of fields, I am expecting a ease way to generate the new table.

Many thanks for any help!

Jane

 

[Andles]

You can create third table with all the fields of both table included by using a SELECT INTO statement.

SELECT * INTO NewTableName
FROM Table1 t1 INNER JOIN Table2 t2
On t1.CommonField = t2.CommonField

This will also duplicate common fields but you can get around this by renaming them after the fact, deleting the one duplicate field. Otherwise list each field you require in the Select Statement in place of *

 

[tlbroadbent]

SQL Server will not allow insertion of duplicate column names so Andles "Select *" suggestion will not work if you have duplicate column names in the two tables.

Here is s simple script that creates a "Select Into" query that lists each column of two joined tables. It eliminates columns with duplicate names. You may want to modify it to add a prefix or suffix instead of eliminating the duplicate column names.

Substitute your table names for those in the sample. The output of the script is the SQL "Select Into" query. Copy and paste it into a Query Analyzer Editor window. Run it to create the new table. You can also edit it before running to further refine it.

--------------------------------

Declare @sql1 nvarchar(4000), @sql2 nvarchar(4000)

Set @sql1='Select '
Set @sql2=''

Select @sql1=@sql1+char(10)+' a.'+Column_Name+','
From Utes.information_schema.columns
Where table_name='table1'

Select @sql2=@sql2+char(10)+' b.'+Column_Name+', '
From Utes.information_schema.columns
Where table_name='table2'
And column_name Not In
(Select Column_name
From Utes.information_schema.columns
Where table_name='table1')

Set @sql2=Left(@sql2,len(@sql2)-1)

Set @sql2=@sql2+ char(10) +
'Into NewTable' + char(10) +
'From table1 a' + char(10) +
'Inner Join table2 b' + char(10) +
'On a.key=b.key'

Select @sql1+@sql2 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jadering]

I really appreciated your help! These tips solved my problem.

Jane