If you do not use the /n or /c options, the bcp utility prompts you for further information, including the storage type, prefix length, and terminator for each column of data being copied. For fields that are to be stored as char or binary, bcp also prompts for a field length.
The default values at these prompts produce exactly the same results as the /n option and provide a way to copy data out of a database for later reloading into SQL Server. If you are copying data to or from SQL Server for use with other programs, your answers to the prompts depend on the format required by the other program.
Note You can skip a table column on input (but not on output) by specifying 0 prefix length, 0 length, and no terminator.
For example, to interactively copy the publishers table to the PUBL_OUT file:
bcp pubs..publishers out publ_out /Sservername /Usa /Ppassword
A series of four prompts appears for each column. This example is for the pub_id column in publishers. For each prompt, the default is displayed within brackets. You can press ENTER to accept the default, or type a new value and then press ENTER.
Enter the file storage type of field pub_id [char]: Enter prefix length of field pub_id [0]: Enter length of field pub_id [4]: Enter field terminator [none]:
The following table shows the defaults and possible responses for each of the four prompts:
| Prompt | Default provided | Possible responses |
|---|---|---|
| File storage type | Database storage type for most fields: char for varchar; binary for varbinary. | char to create or read an ASCII file (any SQL Server datatype where implicit conversion is supported). |
| Prefix length | 0 for fields defined with datatype (not storage type) char and all fixed-length datatypes, 1 for most other datatypes, 2 for binary and varbinary, 4 for text and image. | 0 if no prefix is desired; defaults are recommended in all other cases. |
| Field length | Defined length for char and varchar. Defined length * 2 for binary and varbinary saved as char. Maximum length needed to avoid truncation or data overflow for all other datatypes. | Default values or greater are recommended. |
| Field terminator | None. | Up to 30 ASCII characters or one of the following: \t tab \n newline \r carriage return \0 null terminator \\ backslash |
The responses to these four prompts provide a flexible system for writing a file that requires little or no editing to conform to other data formats or to read files from other software. The following sections describe each of these prompts and the ways they interact to affect the data. The default responses to these prompts are designed to provide the fastest and easiest method of copying data to and from SQL Server.
For more information, choose one of the following topics: