SELECT INTO Question...

[christheprogrammer]

Hiya
I already asked this in SQL Server thread but I realize now that it is better asked here...

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

 

[fluteplr]

Yes, list the fields instead of using select *.

 

[christheprogrammer]

unfortunately, there are over 200 fields in both tables and I don't want to list them all..............

Thanks Chris
grandin1@yahoo.com

 

[tlbroadbent]

MS Access handles such a make table query as you want to do because it prefixes the column names with the table name (i.e., table1_colA). SQL Server does not handle it. I know not whether other database systems will do what you want. 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 again, I appreciate it. I must use SQL Server, so the Access route is out. If the Microsoft people coded a way to prefix table names, then I can too! I am going to try it. Chris
grandin1@yahoo.com

 

[Tim1]

Hiya Chris,

You can prefix column names in SQL server using an alias, so that instead of typing out the table name each time, you use the alias. eg:

SELECT a.field1,
b.field2,
a.field3,
a.field4,
b.field5
INTO new_table
FROM table1 a,
table2 b
WHERE a.field1 = b.field1

There is also a very complicated way of selecting fields for a query using syscolumns and dynamic SQL, but if you can code your columns in, you will make it much easier.
In addition, if you want all columns of a table, you can still do a.* or b.*

HTH

Tim