Import data from ODBC source into SQL-Server via T-SQL
Posted on 2006-03-22
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'
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,