6

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.

Nonpareil
  • 73
  • 1
  • 2
  • 8
  • 7
    Don't use the evil "`SELECT *`" – Strawberry May 19 '15 at 13:41
  • Bearing in mind the above about not using `*`, you could try `firstTable.*, secondTable.*, etc`. But try to only output what you need in your select statements. – Tim Lewis May 19 '15 at 13:42
  • possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) – Politank-Z May 19 '15 at 13:45
  • @Politank-Z In what way is it a duplicate of that question? – Mark Sinkinson May 19 '15 at 13:48
  • @MarkSinkinson My mistake. My mind transposed columns and rows. – Politank-Z May 19 '15 at 13:48
  • If you are attempting what @Ray suggested, you need to use an alias for the second table when specifying `*` else it will grab both tables. – Solomon Rutzky May 19 '15 at 13:50
  • I have attempted the 'firstTable.patID as pID, * FROM...' and all that it does is adds a new column up front but still gives me my patID twice later on in the columns – Nonpareil May 19 '15 at 13:51
  • @Nonpareil How about attempting what Ray suggested, instead of thinking you can (incorrectly) make changes. – Mark Sinkinson May 19 '15 at 14:05
  • @MarkSinkinson I'm starting to think that's what I'll have to do. I have to do this with a ton of queries today and was hoping there might be a way around naming the columns specifically. – Nonpareil May 19 '15 at 14:08
  • @Nonpareil "I have attempted the 'firstTable.patID as pID, * FROM...' and all that it does is adds a new column up front but still gives me my patID twice later on in the columns" Correct, because you did not attempt what Ray suggested, in that you are using `*` by itself and not prefixed with an alias such as `Table2.*`. If the issue is that you have lots of tables, you will have to do `Table2.*, Table3.*,...`, but that can still have the duplicate field issue. In which case just specify only the fields that you want (all being aliased), and no `*`, as I suggested. – Solomon Rutzky May 19 '15 at 14:18
  • 1
    @srutzky When I put that, I missed that he put table2.* and not just * since I haven't seen this method much. Either of your methods will work, I was just assuming there might be a way built in other than to name each individual column. Thanks. – Nonpareil May 19 '15 at 14:31

3 Answers3

3

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.

Ray
  • 40,256
  • 21
  • 101
  • 138
  • 3
    This is the accurate, but unfortunate solution. I think OP and I were hoping for some miracle hack to avoid typing a hundred column names LOL.... – Hack-R Oct 30 '15 at 15:11
3

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...');
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
0

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!

William Halliday
  • 21
  • 3