Import data from ODBC source into SQL-Server via T-SQL

Dear Experts,

I have a ODBC-Source named 'FOO' installed (System-DNS), and it's working fine. Now I need to read in all rows from table 'KODATEN' from that ODBC source and put them into an existing table of same structure in the SQL-Server (ODBC-driver and SQL-Server reside on the same machine).
And, most importantly, it should be done via a T-SQL statement or something else that can be used inside a stored procedure. Solutions along the lines of "click on some wizard in the administration tool" won't be of much help, I'm afraid.
Is there a way to specify the ODBC source in the FROM clause of a SELECT statement?

To further illustrate my needs: I already succeeded in importing data from a CSV-file into SQL-Server by
  BULK INSERT fcp..NTCS FROM 'D:\foo.csv'
  WITH (
     DATAFILETYPE = 'char',
     ROWTERMINATOR = '\n',

Now I need to do the very same thing except reading from ODBC rather than from a CSV-file.

I guess the answer will be rather simple for the experts but it's all the more urgent.
Eagerly looking forward to your support,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

>.Is there a way to specify the ODBC source in the FROM clause of a SELECT statement? <<

you can do it with OPENROWSET.

This example uses the Microsoft OLE DB Provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized from the datasource, user_id, and password, and a SELECT is used to define the row set returned.

USE pubs
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
In BULK INSERT statement  you need to specify the full path of the data file that contains data to copy into the specified table or view. BULK INSERT can copy data from a disk (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name.

I think you should create a linked server to your ODBC Source and then use BULK INSERT


Set up a DTS package and then execute it via T-SQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wlfsAuthor Commented:
Thanks a lot, that definitely seems to be the way to go.
But - please excuse my ignorance in this area - I am not able to figure out the correct datasource/provider_string.

This is the connection string that works perfectly fine in Visual Studio .Net 1.1:

Now I tried various alternatives using 'MSDASQL' as the provider name for OLE DB Provider for ODBC:
select a.* from openrowset('MSDASQL','Nullable=1;DSN=ODBC5_KORE;DSDLL=PWDSMF32.DLL;Y2KSplit=0;DBQ=C:\woolf\fcp\BMD\KORE\;PromptEnvironment=No',KODATEN) as a;
select a.* from openrowset('MSDASQL','DSN=ODBC5_KORE;DBQ=C:\woolf\fcp\BMD\KORE\',KODATEN) as a;
select a.* from openrowset('MSDASQL','DSN=ODBC5_KORE',KODATEN) as a;

All of them behave the same. There is no error but the osql command prompt freezes. No reaction whatsoever, while at the same time I can happily access the ODBC sourve via .net, Access, or Perl.

Do you have an idea? How can I figure out the correct parameter values for the openrowset() function?

Thanks, woolf
wlfsAuthor Commented:
Hmm, openrowset(), linked server, DTS package. Especially linked server with bulk insert seems attractive.
I guess I'll just close this question and take my time to sort this all out.
Altogether I am really stunned that such a seemingly trivial question turns out to be that complicated.

regards, woolf

PS: If you have any hint about the datasource/provider_string please feel still free to post it :). I'd really like to figure that out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.