bcp Command-line Utility

Copies a database table to or from an operating-system file in a user-specified format.

Syntax

bcp [[database_name.]owner.]table_name {in | out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]

where

database_name

Specifies the database. If the table being copied is in your default database, this parameter is optional.

owner

Is the owner's name. This name is optional if you own the table being copied. This parameter is optional. If no owner is specified and you do not own a table of that name, the program will not execute.

table_name

Specifies which database table to copy.

in | out

Specifies the direction of the copy. The in option copies from a file into the database table; the out option copies to a file from the database table.

datafile

Is the full path of an operating-system file when copying a table to or from a hard-disk file or a single diskette. The path can have from 1 through 255 characters. When copying a table to or from multiple diskettes, datafile is a drive specifier only (such as A:).

/m maxerrors

Is the maximum number of errors that can occur before the copy is canceled. Each row that cannot be rebuilt by bcp is ignored and counted as one error. If this option is not included, the default is 10.

/f formatfile

Is the full path of a file with stored responses from a previous use of bcp on the same table; creation of the format file is optional. Use this option when you have already created a format file that you want to use when copying in or out. After you answer format questions, bcp asks whether you want to save your answers in a format file. The default filename is BCP.FMT. The bcp utility can refer to a format file when copying data so that you do not have to duplicate your previous format responses interactively. If this option is not used, bcp queries you for format information.

/e errfile

Is the full path of an error file where bcp stores any rows that it was unable to transfer from the file to the database. Error messages from the bcp utility go to the user's workstation. If this option is not used, no error file is created.

/F firstrow

Specifies the number of the first row to copy (the default is the first row).

/L lastrow

Specifies the number of the last row to copy (the default is the last row).

/b batchsize

Specifies the number of rows per batch of data copied (the default copies all the rows in one batch).

/n

Performs the copy operation using the data's native (database) datatypes as the default. This option does not prompt for each field; it uses the default values.

/c

Performs the copy operation with a character datatype as the default. This option does not prompt for each field; it uses char as the default storage type, no prefixes, \t (tab) as the default field separator, and \n (new line) as the default row terminator.

/E

Is used when identity values are present within the table to be imported.

When importing data, an identity column is temporarily assigned an identity value of 0. As the rows are inserted into the table, SQL Server assigns unique values based on the seed and increment values specified during table creation. Use the /E flag when identity values already exist in the file. If SQL Server-generated identity values are preferred, place only non-identity columns in the file.

/t field_term

Is the default field terminator.

/r row_term

Is the default row terminator.

/i inputfile

Is the name of a file that redirects input to bcp.

/o outputfile

Is the name of a file that receives output redirected from bcp.

/U login_id

Is a login ID.

/P password

Is a user-specified password. If this option is not used, bcp prompts for a password. If this option is used at the end of the command line without any password, bcp uses the default password (NULL).

/S servername

Specifies which SQL Server to connect to. The servername is the name of the server computer on the network. This option is required when you are executing bcp from a remote computer on the network.

/v

Reports the current DB-Library version number.

/a packet_size

Is the number of bytes, per network packet, sent to and from the server. A server configuration option can be set using SQL Enterprise Manager (or the sp_configure system stored procedure). However, this option can be overridden on an individual basis with the bcp /a option. The valid values for packet_size are 512 through 65535. The server default is 4096; the default value for the Windows NT - based version of bcp is 4096. The default value for MS-DOS is 512, although larger sizes can be requested.

Increased packet size may enhance performance on bulk copy operations. Microsoft testing indicates that 4096 to 8192 is typically the fastest setting for bulk copy operations. If a larger packet is requested but cannot be granted, the client will default to 512. The performance statistics generated at the end of a bcp run will show the actual packet size used.