How to SELECT * INTO x FROM y join z ?????

[christheprogrammer]

Hiya
I want to do this:

SELECT * INTO [##table1] FROM [dbo].[##table2]
INNER JOIN [##table3]
ON [##table2].field1 =[##table3].field1)

keep in mind that [##table2].field1 and [##table3].field1
have the same column name. It is impossible to create a table having 2 columns with the same name. Is it possible to do this select statement somehow but only with one of the two fields in the result set?
Thanks a bunch,

Chris

Chris Grandin
grandin1@yahoo.com

http://www.mala.bc.ca

Malaspina has a great computer science program. Top-Notch.

 

[tlbroadbent]

Try this.

SELECT a.field1, a.field2, a.filed3, b.field1 As field1b, b.field2 as field2b, ...
INTO ##table1
FROM ##table2 a
INNER JOIN ##table3 b
ON a.field1 = b.field1

Or if field1 is the only duplicated column then you could do the following.

SELECT a.*, b.fieldA, b.fieldB, ... b.fieldN
INTO ##table1
FROM ##table2 a
INNER JOIN ##table3 b
ON a.field1 = b.field1
Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[christheprogrammer]

Thanks for the swift reply, unfortunately both tables have over 200 fields, and I want all of them included except the one I am using for the join... See the dilemma? This must have come up before. I cannot see how SQL could have survived without some mechanism for this scenario. I really want to do this:

SELECT a.*, b.1,b.2,b.3,b.4,........ INTO ... etc

but without writing 200 b.x 's
Help would be greatly appreciated!
Thanks
Chris Chris Grandin
grandin1@yahoo.com

 

[tlbroadbent]

I'm not aware of any way to exclude or alias one column in SQL Server. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[dbmsguy]

why not dynamically build the sql statement using a query to sysobjects? at any rate, there is no way to exclude columns and besides that, it is bad programming to use 'select * ' anyway.


Paul

http://www.dbmsguy.com