Q
Problem solve Get help with specific problems with your technologies, process and projects.

Writing stored procedures in SQL Server to import data

How do you write stored procedures in SQL Server to import data from other servers, such as Oracle and Access as well as from text files. Setting up the source server as a Linked Server makes the task easier.

Can a stored procedure be used to import data from other types of database's and text files to SQL Server?
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.
This was last published in July 2005

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close