SQL Server BCP is a command line utility that bulk copies data between instances of SQL Server and data files.  Data can be imported into SQL Server tables or exported to a data file.  The BCP utility can also be used to generate format files which specify the number and type of data columns.  The BCP utility can quickly transfer large amounts of data.

The syntax of a BCP command is shown below:

The first argument specifies the data source or destination.  Data can be exported directly from a table, view or query and imported into a table or view.

The second argument specifies the direction of the bulk copy.  The options are:

• in <data_file>: copies data from <data_file> to the specified table or view.
• out <data_file>: copies data from the specified table or view into <data_file>.
• queryout <data_file>: copies data from a query into <data_file>.
• formal nul: creates a format file based on the option specified (-n, -c, -w, or -N) and table or view.

<data_file> is a .txt file that stores the data to be imported/exported.

Definitions of some useful arguments are included below:

• -c: Performs an operation using the character data type.  Note: by default \t (tab) is the field separator and \r\n (newline character) is the row terminator.  If your data may contain either of these characters, you should override the field separator and row terminator by using the –r (row term) and –t (field term) arguments.
• -e <err_file>: Specifies the path to an error file location that stores rows the bcp utility could not transfer.
• -f <format_file>: Specifies the full path of a format file.  Can be used in two ways:
  • If -f is used with the format option, the specified format_file is created for the specified table or view.
  • If used with the in or out option, -f requires an existing format file.
• -n: Performs the bulk-copy operation using the native (database) data types of the data.
• -N: Performs the bulk-copy operation using the native (database) data types of the data for non-character data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one instance of SQL Server to another using a data file.
• -r <row_terminator>: Specifies the row terminator. The default is \n (newline character).
• -t : Specifies the field terminator. The default is \t (tab character).
• -T: Specifies the bcp utility connects to SQL Server with a trusted connection using integrated security.
• -w: Performs the bulk copy operation using Unicode characters.

The following are some usage examples:

bcp <DB>.[dbo].<Table> in '<DataFile>.txt' -f '<FormatFile>.fmt' –T

Imports data into table

bcp <DB>.[dbo].<Table> format nul -c -f <FormatFile>.fmt -t #$# -r #@# –T

bcp 'SELECT [Col1], [Col2], [Col3],… FROM <Table>' queryout '<DataFile>.txt' -c -t #$# -r #@# -T