To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
Absolutely! I've written many stored procedures to import data from other servers, usually Oracle and Access as well as from text files. When importing data from other servers, you'll have an easier time if you set up the source server as a Linked Server (see SQL Server Books Online for more details).
Once you've set up the Linked Server, you can write SQL statements directly against the Linked Server. For example, you could write the statement INSERT INTO local_table…SELECT col1, col2,… FROM linked_server.db1.dbo.foo.
When importing data from text files, simply use the BULK INSERT statement or, my preference, call the BCP command line utility using XP_CMDSHELL within the stored procedure. It takes a little work to get the exact XP_CMDSHELL string correct. All you have to do is encapsulate the BCP command-line string within a parameter for XP_CMDSHELL extended stored procedure.
Do you have comments on this Ask the Expert Q&A? Let us know.
|
