Duplicate Column Names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Duplicate Column Names

Author

Topic

cgoatly
Starting Member

2 Posts

Posted - 2006-06-29 : 06:47:11

Hi,

I have written a query with the following structure:

create table a as

select s.*, e.*

from table1 s
inner join table2 e
on s.pk = e.pk

where blah blah blah........

Now it gives me the error "duplicate column names" which I understand as there are some fields with the same name in table.
However aside from manually listing all the field names and renaming them to avoid clashes I cannot find a way around this.
I think logically there MUST be an easier way of doing this than writing the names out long-hand....

Can anyone help me with this please???!!!!

Thanks a lot,

Caroline

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 06:51:26

If both tables have columns with same name you will get that error. Use alias Name

If there is column name Id in each table then use

Select s.id as SID,e.id as Eid,......
from......

Madhivanan

Failing to plan is Planning to fail

cgoatly
Starting Member

2 Posts

Posted - 2006-06-29 : 07:02:47

Thanks for that. However I was trying to avoid writing alias's for the column names. I realise I could, but the tables have lots of fields and I don't want to spend a lot of time having to write them all out. I thought there may be any easy way, something along the lines of:

select s.* as s, e.* as e

which would add s or e respectively to the list of fields in each. that does not actually work but I wanted to know if there was anything along those lines that you could do.....

Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 07:38:02

quote:
Originally posted by cgoatly

Hi,

I have written a query with the following structure:

create table a as

select s.*, e.*

from table1 s
inner join table2 e
on s.pk = e.pk

SELECT * INTO Table1 creates a new table. But you will still have to deal with duplicate names.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-29 : 07:38:31

Unfortunately, there is no way to avoid writing the columns out. However, you can use something like this to generate the text and save your typing...

declare @s varchar(8000)
select @s = isnull(@s + ', ', '') + COLUMN_NAME + ' as APrefix_' + COLUMN_NAME
from information_schema.columns where TABLE_NAME = 'table name'
select @s

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-29 : 07:40:31

use this to get a list of your column names



select table_name, column_name
from information_schema.columns
where table_name= 'tablenamehere'

"I don't want to spend a lot of time having to write them all out"
Good programming practice says, "Tuff, write them out".

*EDIT* bah

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources