Choosing a Tool to Import or Export Data

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