We help IT Professionals succeed at work.

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

wlfs asked
Medium Priority
Last Modified: 2012-08-13
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,
Watch Question

>.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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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


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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.