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

Posted on 2006-03-22
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,
Question by:wlfs
    LVL 20

    Expert Comment

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

    you can do it with OPENROWSET.
    LVL 20

    Expert Comment


    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
    SELECT a.*
    FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
    'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
    LVL 20

    Accepted Solution

    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
    LVL 5

    Author Comment

    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
    LVL 5

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now