Choosing a Tool to Import or Export Data

Data can be imported and exported from Microsoft® SQL Server™ using several tools and Transact-SQL statements. You can also write your own programs to import and export data using the programming models and application programming interfaces (APIs) available with SQL Server.

Methods for copying data to and from SQL Server include:

• Using the Data Transformation Services (DTS) Import and Export wizards or DTS Designer to create a DTS package that can be used to import or export data.

  The DTS package can also transform data during the import or export process.

• Using SQL Server replication to distribute data across an enterprise.

  The replication technology in SQL Server allows you to make duplicate copies of your data, move those copies to different locations, and synchronize the data automatically so that all copies have the same data values. Replication can be implemented between databases on the same server or different servers connected by LANs, WANs, or the Internet.

  For more information, see Overview of Replication.

• Using the bcp command prompt utility to import and export data between SQL Server and a data file.
• Using the BULK INSERT statement to import data from a data file to SQL Server.
• Using the SELECT INTO statement to create a new table based on an existing table.

  For more information, see SELECT.

It is possible to select data from an arbitrary OLE DB provider, allowing data to be copied from external data sources into SQL Server.

• Using the INSERT statement to add data to an existing table.

  For more information, see INSERT.

A distributed query that selects data from another data source can also be used to specify the data to be inserted.

For more information, see Distributed Queries.

The method chosen to import or export data depends on a variety of user requirements, the most common being:

• The format of the source and destination data.
• The location of the source and destination data.
• Whether the import or export is a one-time occurrence or an ongoing task.
• Whether a command prompt utility, Transact-SQL statement, or graphical interface is preferred (ease-of-use).
• The performance of the import or export operation.

See Also