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.