wlfs
asked on
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',
FIELDTERMINATOR = ';',
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,
woolf
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',
FIELDTERMINATOR = ';',
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,
woolf
Example
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
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sea
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
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
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sea
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
Nullable=1;DSN=ODBC5_KORE;
Now I tried various alternatives using 'MSDASQL' as the provider name for OLE DB Provider for ODBC:
select a.* from openrowset('MSDASQL','Null
select a.* from openrowset('MSDASQL','DSN=
select a.* from openrowset('MSDASQL','DSN=
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
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:
Nullable=1;DSN=ODBC5_KORE;
Now I tried various alternatives using 'MSDASQL' as the provider name for OLE DB Provider for ODBC:
select a.* from openrowset('MSDASQL','Null
select a.* from openrowset('MSDASQL','DSN=
select a.* from openrowset('MSDASQL','DSN=
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
ASKER
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
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
you can do it with OPENROWSET.
http://doc.ddart.net/mssql/sql70/oa-oz_5.htm