Export (0) Print
Expand All

Choosing a Tool to Import or Export Data

SQL Server 2000

Data can be imported to and exported from instances of Microsoft® SQL Server™ using several SQL Server 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.

You can copy data to and from instances of SQL Server by:

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

    For more information, see DTS Tools.

  • 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 Replication Overview.

  • Using the bcp command prompt utility to import and export data between an instance of SQL Server and a data file.

  • Selecting data from an OLE DB provider and copying it from external data sources into an instance of SQL Server.

  • Using a distributed query to select data from another data source and specify the data to be inserted.

    For more information, see Distributed Queries.

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

    For more information, see INSERT.

  • Using the BULK INSERT statement to import data from a data file to an instance of SQL Server.

    For more information, see BULK INSERT.

  • Using the SELECT INTO statement to create a new table based on an existing table.

    For more information, see SELECT.

The method chosen to import or export data depends on user requirements, for example:

  • 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.

  • The performance of the import or export operation.

This table describes the capabilities of various import and export options in SQL Server.


Required functionality

DTS wizards


Replication


bcp

BULK INSERT
SELECT INTO/ INSERT
Import text data YES YES YES YES 1
Export text data YES YES
Import from ODBC data sources YES YES
Export to ODBC data sources YES YES
Import from OLE DB data sources YES YES YES (1)
Export to OLE DB data sources YES YES YES
Graphical user interface (GUI) YES YES
Command prompt/batch scripts YES YES YES
Transact-SQL scripts YES YES YES
Automatic scheduling YES YES YES 2 YES 2
Ad hoc import/export YES YES YES YES
Recurring import/export YES YES YES
Maximum performance YES YES
Data transformation YES
Programmatic interface YES YES YES

1 Using a distributed query that retrieves data from an external source by using an OLE DB provider.
2 By explicitly creating a job scheduled using SQL Server Agent.

See Also

bcp Utility

Show:
© 2015 Microsoft