How to remove / rename a duplicate column in SQL (not duplicate rows)
While trying to execute OPENQUERY from Sybase to Microsoft SQL, I encountered the error:
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. Column name "PatientID" is a duplicate.
The query I built joins 2 tables based on similar admissionID and clientID.
For an instance:
PatID AdmID Loc PatID AdmID Doctor
1 5 NC 1 5 Smith
2 7 SC 2 7 Johnson
The real query, of course, contains more information than just that.
Is there a good way to rename or remove one of the AdmID and PatID columns?
I tried:
SELECT * INTO #tempTable
ALTER #tempTable
DROP COLUMN PatID
This does not work as the PatID is ambiguous.
I also tried:
SELECT firstTable.PatID as 'pID', * FROM...
This doesn't work either.
source to share
You will need to alias one of the two duplicate columns and explicitly specify the specific columns in the selection for at least one of the tables (the one you used in its column):
SELECT firstTable.PatID as 'pID', firstTable.column2, secondTable.* FROM...
Please note that I was still using the wildcard on secondTable.
However....
I would not use wildcards at all *
and try to always specify exactly the columns you need.
source to share
Duplicate and missing column names are allowed in result sets, but not in table definitions (although given the error message, it looks like they won't even be allowed in the result set for this situation). Hence, you cannot use the construct SELECT *
if one of these things happens. 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...');
source to share