This is not strictly a SQL problem... here's the why of this question...
the original table I'm trying to get data from is on an old DBase format and I have absolutly no way of changing it's design, but I need to import those data on a regular basis and was planning to do so with the help of DTS. Everything seemed cool and fine until the point I noticed with amazement then with horror that 3 columns have the exact same name(a thing that SQL Server would never allow) !!!!!!!!! when I try to copy the content of each of those fields into a properly designed SQL Table the assistant get confused and can't handle the request properly. Hence my question, I now want to write a request in the form of
SELECT [column1] as meaningfulname1, [column2] as meaningfulname2 from messedupDBaseExportedFile
in order to be able to smoothly and automatically push the data into the new datawarehouse.
No columns will never be added and the actual form of the table and order in which I get all the columns is set in stone and will never ever change. I fully understant why it's a BAD thing in a real world modern application but I MUST import those datas and it's not a one shot job so I need to be able to make it automatic.
Hope that time I will get some answer and no more questions :-) Even if I understand your concerns.
I'm surprised that DBase allows tables sharing the same column name. However, there might be a explanation:
following the DBase table specification, columns were allowed names up to 10 chars but newer versions introduced enhancements that increased this limit. I would think that this is the case. Perhaps DTS does not support this feature and truncates at 10.
Which version is the DBase-db?
As an alternative: can you create a view in the DBase-db that provides access to the table by different column names?
Its not DTS who truncates the table names, it happens at the export time, from the original software who hosts the datas. The columns name are originally unique but longer than 10 chars and the exported file, in DBaseIII format, already has the column names truncated. The odd thing is that I cannot open the file in Access (cannot import a field more than once) but DTS is somehow able to manage that, and present me with the table structure with 3 equally named columns, and that was the reason why I thought I could create a SQL request that would be fit to push the datas in a SQL Table