CODEWITHSUNDEEP
sqlsql-serversybase
Nonpareil
Nonpareil

Reputation: 73

How to remove/rename a duplicate column in SQL (not duplicate rows)

When attempting to do an OPENQUERY from Sybase to Microsoft SQL I run into an error:

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "PatientID" is a duplicate.

The query I have built joins 2 tables based on similar admissionID and patientID.

For Instance:

PatID   AdmID   Loc  PatID  AdmID   Doctor 
1         5      NC    1      5      Smith 
2         7      SC    2      7      Johnson

The real query of course has a ton more information than just this.

Is there a good way to rename or remove one of the AdmID and PatID columns?

I attempted:

SELECT * INTO #tempTable
ALTER #tempTable
DROP COLUMN PatID

This does not work since PatID is ambiguous.

I also attempted:

SELECT firstTable.PatID as 'pID', * FROM...

This does not work either.

Upvotes: 6

Views: 16640

Answers (3)

William Halliday
William Halliday

Reputation: 21

I also had this problem and as there are many columns in the two tables and i am not sure which i needed or how many were duplicates i was not able to type them all out.

anyway i have managed to find a somewhat inelegant solution which works and should work for the above example you described. seems that duplicate column names are not a problem in normal SQL, just the open query part. so i ran the same query twice selecting all from first table then all from second table, dumping both into a temp tables and then did a select all on a join of the temp tables:

Select * into #T1 from OPENQUERY(TOAD, 
'select T1.* from t1, t2 where T1.ID = T2.ID')

Select * into #T2 from OPENQUERY(TOAD, 
'select T2.* from t1, t2 where T1.ID = T2.ID')

select * from #T1 inner join #T2 where #T1.ID = #T2.ID

drop table #T1
drop table #T2

hope this helps!

Upvotes: 0

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

Duplicate and missing column names are allowed in result sets but not in table definitions (although given the error message, it looks like they wouldn't even be allowed in the result set for this situation anyway). Hence you cannot use the SELECT * construct if either of these things occur. You need to specify the fields so that you can control the field names as they appear in the result set.

SELECT *
INTO   #TempTable
FROM   OPENQUERY('SELECT tab1.Field1, tab1.Field2, tab2.Field12...');

Upvotes: 3

Ray
Ray

Reputation: 41428

You'll have to alias one of the two duplicate columns and explicitly indicate specific columns in the select at least for one of the tables (the one you've used the alias on it's column):

 SELECT firstTable.PatID as 'pID', firstTable.column2, secondTable.* FROM...

Notice, I've still used a wildcard on the secondTable.

However....

I'd avoid using * wildcards altogether and try to always indicate exactly the columns you need.

Upvotes: 4

Related Questions