Import and export bulk data using bcp (SQL Server)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This article provides an overview of 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.

To use bcp to bulk import data, you must understand the schema of the table and the data types of its columns, unless you're 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 utility provides switches that you use to specify the data type of the data file and other information. If you don't specify these switches, bcp prompts for formatting information, such as the type of data fields in a data file. It 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 runs for equivalent data files. For more information, see Specify compatibility data formats when using bcp.

For a description of the bcp syntax, see bcp Utility.

Examples

• bcp utility

• Data formats for bulk import or bulk export:

  • Use native format to import or export data
  • Use character format to import or export data
  • Use Unicode Native Format to Import or Export Data
  • Use Unicode character format to import or export data

• Specify field and row terminators

• Keep nulls or default values during bulk import

• Keep identity values when bulk importing data

• Format files for importing or exporting data (SQL Server)

  • Create a format file with bcp
  • Use a format file to bulk import data
  • Use a format file to skip a table column
  • Use a format file to skip a data field
  • Use a format file to map table columns to data-file fields

• Examples of bulk import and export of XML documents

Related content

• INSERT
• SELECT clause
• bcp utility
• Prepare to Bulk Import Data (SQL Server)
• BULK INSERT
• Bulk Import and Export of Data (SQL Server)
• OPENROWSET
• Create a format file with bcp (SQL Server)