Running bcp

The bcp utility transfers data between an existing database table or view and an operating-system file in a format that can be specified by the user. The bcp utility is executed from the operating system.

In general, you must supply the following information for transferring data to and from SQL Server:

In addition, for each column, you can modify the datatype, length, and terminator.

    To transfer data with the bcp utility

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 name. This name is optional if the table or view being copied is in your default database. Otherwise, you must specify a database name.
owner
Is the owner's name. This name is optional if you own the table or view being copied. 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 or view 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 or view.
datafile
Is the full path of an operating-system file when copying to or from a hard-disk file or a single diskette. The path can have from 1 to 255 characters. To copy 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 thrown out 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 to copy 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 you copy 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
Is the number of the first row to copy (the default is the first row).
/L lastrow
Is the number of the last row to copy (the default is the last row).
/b batchsize
Is 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 if identity values already exist in the file. If SQL Server-generated identity values are preferred, place only nonidentity columns in the file.

This option is in effect only during bcp in. It has no effect during bcp out.

/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 the /P option is not used, bcp prompts for a password. If the /P 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, and the default value for the Windows NT - based bcp is 4096. The default value is 512 for MS-DOS, though 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. Packet sizes over 512 will work only for clients connected to a Windows NT - based SQL Server. 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.