If you do not use the /n or /c options, but instead set the bcp default values yourself after information is gathered about each field in the table, bcp prompts you to save a format file and then prompts you for the filename. For example:
Do you want to save this format information in a file? [Y/n] y Host filename: [bcp.fmt]
This format file can be used to copy the data back into SQL Server or to copy data out from the table at another time. When you are copying data in or out using an existing format file, you are not prompted for information; the format file provides the information needed.
The default name for the format file is BCP.FMT, but you can specify a different filename. For more information about using format files, see Using a Format File to Selectively Copy Data, later in this chapter.
A typical format file is shown below.
The following table explains each area of the bcp format file:
| Field | Field description |
|---|---|
| Version | Version number of bcp. |
| Prefix length | Used to specify the most efficient file storage. For ASCII data files, use 0. A 4 represents the length of the particular data file field. |
| Terminator | A delimiter to separate the fields in a data file. Common terminators are commas and end of line (\r \n). |
| Server column name | The name of the field taken from the SQL Server table. It is not necessary to use the actual name of the field. The only condition is that the field not be blank. |
| Number of columns | The number of columns in the data file. |
| Host file column order | The field number of the data file. |
| Host file datatype | The datatype stored in the particular field of the data file. With ASCII data files, use sqlchar; for native format data files, use default datatypes. |
| Host file data length | The length, in bytes, of the datatype stored in the particular field of the data file. |
| Server column order | The order in which columns appear in the SQL Server database. |