How to use the bcp utility

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

The bulk copy program utility (bcp) bulk copies data between an instance of SQL Server and a data file in a user-specified format.

Remarks

• For information about where to find or how to run the bcp utility and about the command prompt utilities syntax conventions, see SQL command-line utilities (Database Engine).

• For information on preparing data for bulk import or export operations, see Prepare data for bulk export or import.

• For information about when row-insert operations that are performed by bulk import are logged in the transaction log, see Prerequisites for minimal logging in bulk import.

• The characters <, >, |, &, and ^ are special command shell characters, and they must be preceded by the escape character (^), or enclosed in quotation marks when used in a string (for example, "StringContaining&Symbol"). If you use quotation marks to enclose a string that contains one of the special characters, the quotation marks are set as part of the environment variable value. For more information, see Using additional special characters.

Native data file support

In SQL Server, the bcp utility supports native data files compatible with SQL Server versions starting with SQL Server 2000 (8.x) and later.

Computed columns and timestamp columns

Values in the data file being imported for computed or timestamp columns are ignored, and SQL Server automatically assigns values. If the data file doesn't contain values for the computed or timestamp columns in the table, use a format file to specify that the computed or timestamp columns in the table should be skipped when importing data; SQL Server automatically assigns values for the column.

Computed and timestamp columns are bulk copied from SQL Server to a data file as usual.

Specify identifiers that contain spaces or quotation marks

SQL Server identifiers can include characters such as embedded spaces and quotation marks. Such identifiers must be treated as follows:

• When you specify an identifier or file name that includes a space or quotation mark at the command prompt, enclose the identifier in quotation marks ("").

  For example, the following bcp out command creates a data file named Currency Types.dat:

  bcp AdventureWorks2022.Sales.Currency out "Currency Types.dat" -T -c
  

• To specify a database name that contains a space or quotation mark, you must use the -q option.

• For owner, table, or view names that contain embedded spaces or quotation marks, you can either:

  • Specify the -q option, or

  • Enclose the owner, table, or view name in brackets ([]) inside the quotation marks.

Data validation

bcp now enforces data validation and data checks that can cause scripts to fail if they're executed on invalid data in a data file. For example, bcp now verifies that:

• The native representations of float or real data types are valid.

• Unicode data has an even-byte length.

Forms of invalid data that could be bulk imported in earlier versions of SQL Server can fail to load now; whereas, in earlier versions, the failure didn't occur until a client tried to access the invalid data. The added validation minimizes surprises when querying the data after bulkload.

Bulk exporting or importing SQLXML documents

To bulk export or import SQLXML data, use one of the following data types in your format file.

Character mode (-c) and native mode (-n) best practices

This section has recommendations for character mode (-c) and native mode (-n).

• (Administrator/User) When possible, use native format (-n) to avoid the separator issue. Use the native format to export and import using SQL Server. Export data from SQL Server using the -c or -w option if you plan to export the data to a non-SQL Server database.

• (Administrator) Verify data when using bcp out. For example, when you use bcp out, bcp in, and then bcp out verify that the data is properly exported and the terminator values aren't used as part of some data value. Consider overriding the default terminators (using -t and -r options) with random hexadecimal values to avoid conflicts between terminator values and data values.

• (User) Use a long and unique terminator (any sequence of bytes or characters) to minimize the possibility of a conflict with the actual string value. This can be done by using the -t and -r options.

Examples

The examples in this section make use of the WideWorldImporters sample database for SQL Server 2016 (13.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance. WideWorldImporters can be downloaded from https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0. See RESTORE Statements for the syntax to restore the sample database.

Example test conditions

Except where specified otherwise, the examples assume that you use Windows Authentication and have a trusted connection to the server instance on which you're running the bcp command. A directory named D:\bcp is used in many of the examples. Replace <server_name> and other placeholder values with values for your environment.

The following Transact-SQL script creates an empty copy of the WideWorldImporters.Warehouse.StockItemTransactions table and then adds a primary key constraint:

USE WideWorldImporters;
GO

SET NOCOUNT ON;

IF NOT EXISTS (SELECT *
               FROM sys.tables
               WHERE name = 'Warehouse.StockItemTransactions_bcp')
    BEGIN
        SELECT *
        INTO WideWorldImporters.Warehouse.StockItemTransactions_bcp
        FROM WideWorldImporters.Warehouse.StockItemTransactions
        WHERE 1 = 2;

        ALTER TABLE Warehouse.StockItemTransactions_bcp
        ADD CONSTRAINT PK_Warehouse_StockItemTransactions_bcp
            PRIMARY KEY NONCLUSTERED (StockItemTransactionID ASC);
    END

You can truncate the StockItemTransactions_bcp table as needed:

TRUNCATE TABLE WideWorldImporters.Warehouse.StockItemTransactions_bcp;

A. Identify bcp utility version

At a command prompt, enter the following command:

bcp -v

B. Copy table rows into a data file (with a trusted connection)

The following examples illustrate the out option on the WideWorldImporters.Warehouse.StockItemTransactions table.

• Basic

  This example creates a data file named StockItemTransactions_character.bcp and copies the table data into it using character format.

  At a command prompt, enter the following command:

  bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\bcp\StockItemTransactions_character.bcp -c -T
  

• Expanded

  This example creates a data file named StockItemTransactions_native.bcp and copies the table data into it using the native format. The example also: specifies the maximum number of syntax errors, an error file, and an output file.

  At a command prompt, enter the following command:

  bcp WideWorldImporters.Warehouse.StockItemTransactions OUT D:\bcp\StockItemTransactions_native.bcp -m 1 -n -e D:\bcp\Error_out.log -o D:\bcp\Output_out.log -S <server_name> -T
  

Review Error_out.log and Output_out.log. Error_out.log should be blank. Compare the file sizes between StockItemTransactions_character.bcp and StockItemTransactions_native.bcp.

C. Copy table rows into a data file (with mixed-mode authentication)

The following example illustrates the out option on the WideWorldImporters.Warehouse.StockItemTransactions table. This example creates a data file named StockItemTransactions_character.bcp and copies the table data into it using character format.

The example assumes that you use mixed-mode authentication, and you must use the -U switch to specify your login ID. Also, unless you're connecting to the default instance of SQL Server on the local computer, use the -S switch to specify the system name and, optionally, an instance name.

At a command prompt, enter the following command: (The system prompts you for your password.)

bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\bcp\StockItemTransactions_character.bcp -c -U<login_id> -S<server_name\instance_name>

D. Copy data from a file to a table

The following examples illustrate the in option on the WideWorldImporters.Warehouse.StockItemTransactions_bcp table using files created previously.

• Basic

  This example uses the StockItemTransactions_character.bcp data file previously created.

  At a command prompt, enter the following command:

  bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp IN D:\bcp\StockItemTransactions_character.bcp -c -T
  

• Expanded

  This example uses the StockItemTransactions_native.bcp data file previously created. The example also uses the TABLOCK hint, and specifies the batch size, the maximum number of syntax errors, an error file, and an output file.

  At a command prompt, enter the following command:

  bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp IN D:\bcp\StockItemTransactions_native.bcp -b 5000 -h "TABLOCK" -m 1 -n -e D:\bcp\Error_in.log -o D:\bcp\Output_in.log -S <server_name> -T
  

  Review Error_in.log and Output_in.log.

E. Copy a specific column into a data file

To copy a specific column, you can use the queryout option. The following example copies only the StockItemTransactionID column of the Warehouse.StockItemTransactions table into a data file.

At a command prompt, enter the following command:

bcp "SELECT StockItemTransactionID FROM WideWorldImporters.Warehouse.StockItemTransactions WITH (NOLOCK)" queryout D:\bcp\StockItemTransactionID_c.bcp -c -T

F. Copy a specific row into a data file

To copy a specific row, you can use the queryout option. The following example copies only the row for the person named Amy Trefl from the WideWorldImporters.Application.People table into a data file Amy_Trefl_c.bcp.

At a command prompt, enter the following command:

bcp "SELECT * from Application.People WHERE FullName = 'Amy Trefl'" queryout D:\bcp\Amy_Trefl_c.bcp -d WideWorldImporters -c -T

G. Copy data from a query to a data file

To copy the result set from a Transact-SQL statement to a data file, use the queryout option. The following example copies the names from the WideWorldImporters.Application.People table, ordered by full name, into the People.txt data file.

At a command prompt, enter the following command:

bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\bcp\People.txt -t, -c -T

H. Create format files

The following example creates three different format files for the Warehouse.StockItemTransactions table in the WideWorldImporters database. Review the contents of each created file.

At a command prompt, enter the following commands:

REM non-XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_c.fmt -c -T

REM non-XML native format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_n.fmt -n -T

REM XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_c.xml -x -c -T

For more information, see Use non-XML format files (SQL Server) and XML format files (SQL Server).

I. Use a format file to bulk import with bcp

To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the in option. For example, the following command bulk copies the contents of a data file, StockItemTransactions_character.bcp, into a copy of the Warehouse.StockItemTransactions_bcp table by using the previously created format file, StockItemTransactions_c.xml.

At a command prompt, enter the following command:

bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp in D:\bcp\StockItemTransactions_character.bcp -L 100 -f D:\bcp\StockItemTransactions_c.xml -T

J. Specify a code page

The following partial code example shows bcp import while specifying a code page 65001:

bcp MyTable in "D:\data.csv" -T -c -C 65001 -t , ...

K. Example output file using a custom field and row terminators

This example shows two sample files, generated by bcp using custom field and row terminators.

1. Create a table dbo.T1 in the tempdb database, with two columns, ID and Name.

   USE tempdb;
   GO
   
   CREATE TABLE dbo.T1
   (
       ID INT,
       [Name] NVARCHAR (20)
   );
   GO
   
   INSERT INTO dbo.T1 VALUES (1, N'Natalia');
   INSERT INTO dbo.T1 VALUES (2, N'Mark');
   INSERT INTO dbo.T1 VALUES (3, N'Randolph');
   GO
   

2. Generate an output file from the example table dbo.T1, using a custom field terminator.

   In this example, -t , specifies the custom field terminator. Replace <server_name> with a value for your environment.

   bcp dbo.T1 out T1.txt -T -S <server_name> -d tempdb -w -t ,
   

   Here's the result set.

   1,Natalia
   2,Mark
   3,Randolph
   

3. Generate an output file from the example table dbo.T1, using a custom field terminator and custom row terminator.

   In this example, -t , specifies the custom field terminator, and -r : specifies the custom row terminator. Replace <server_name> with a value for your environment.

   bcp dbo.T1 out T1.txt -T -S <server_name> -d tempdb -w -t , -r :
   

   Here's the result set.

   1,Natalia:2,Mark:3,Randolph:
   

   Note

   The row terminator is always added, even to the last record. The field terminator, however, isn't added to the last field.

Additional examples

The following articles contain examples of using bcp:

• Data formats for bulk import or bulk export (SQL Server)

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

• Specify field and row terminators (SQL Server)

• Keep nulls or default values during bulk import (SQL Server)

• Keep identity values when bulk importing data (SQL Server)

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

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

• Examples of bulk import and export of XML documents (SQL Server)

Related content

• Prepare data for bulk export or import
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)
• sp_configure (Transact-SQL)
• sp_tableoption (Transact-SQL)
• Format files to import or export data (SQL Server)

Get help

• Ideas for SQL: Have suggestions for improving SQL Server?
• Microsoft Q & A (SQL Server)
• DBA Stack Exchange (tag sql-server): Ask SQL Server questions
• Stack Overflow (tag sql-server): Answers to SQL development questions
• Microsoft SQL Server License Terms and Information
• Support options for business users
• Additional SQL Server help and feedback

Contribute to SQL documentation

Did you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.

For more information, see Edit Microsoft Learn documentation.