Importing and Exporting Bulk Data by Using the bcp Utility
This topic provides an overview for using the bcp utility to export data from anywhere in a SQL Server database where a SELECT statement works, including partitioned views.
The bcp utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) API. The bcp utility performs the following tasks:
- Bulk exports data from a SQL Server table into a data file.
- Bulk exports data from a query.
- Bulk imports data from a data file into a SQL Server table.
- Generates format files.
The bcp utility is accessed by the bcp command. To use the bcp command to bulk import data, you must understand the schema of the table and the data types of its columns, unless you are using a pre-existing format file.
The bcp utility can export data from a SQL Server table to a data file for use in other programs. The utility can also import data into a SQL Server table from another program, usually another database management system (DBMS). The data is first exported from the source program to a data file and then, in a separate operation, copied from the data file into a SQL Server table.
The bcp command provides switches that you use to specify the data type of the data file and other information. If these switches are not specified, the command prompts for formatting information, such as the type of data fields in a data file. The command then asks whether you want to create a format file that contains your interactive responses. If you want flexibility for future bulk-import or bulk-export operations, a format file is often useful. You can specify the format file on later bcp commands for equivalent data files. For more information, see Specifying Data Formats for Compatibility by Using bcp.
Note
Starting with Microsoft SQL Server version 7.0, the bcp utility is written by using the ODBC bulk-copy API. Earlier versions of bcp were written by using the DB-Library bulk copy API.
For a description of the bcp syntax, see bcp Utility.
Examples
For bcp examples, see:
- bcp Utility
- Copying Data Between Different Collations
- Creating a Format File
- Examples of Bulk Importing and Exporting XML Documents
- Keeping Identity Values When Bulk Importing Data
- Keeping Nulls or Using Default Values During Bulk Import
- Specifying Field and Row Terminators
- Using a Format File to Bulk Import Data
- Using Character Format to Import or Export Data
- Using Native Format to Import or Export Data
- Using Unicode Character Format to Import or Export Data
- Using Unicode Native Format to Import or Export Data
See Also
Concepts
Basic Guidelines for Bulk Importing Data
Importing and Exporting Bulk Data
Creating a Format File
Other Resources
INSERT (Transact-SQL)
SELECT Clause (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|