Elements of the bcp format file

The following list names the various elements of a bcp format file. Use Figure 4-3 as the format file example.

The Tabular Data Stream (TDS) version is always the first line of the file. It specifies the version of TDS that you are using, not the Adaptive Server version, and appears is a literal string without quotation marks. In Figure 4-3, the version is 10.0.
The second line of a bcp format file is the number of columns, which refers to the number of records in the format file, not including lines 1 and 2. Each column in the host table has one line.
One line for each column follows the first and second lines in the database table. Each line consists of elements that are usually separated by tabs, except for the host file datatype and the prefix length which are usually separated by a space. These elements are:
- Host file column order
- Host file datatype
- Prefix length
- Host file data length
- Terminator
- Server column order
- Server column name
- Column precision
- Column scale

The following sections describe the column elements in the format file.

Host file column order

The host file column order is the sequential number of the field in the host data file, which begins numbering at 1.

Host file datatype

The host file datatype refers to the storage format of the field in the host data file, not the datatype of the database table column.

Table 4-7 lists the valid storage formats.

**Table 4-7: Host file datatype storage format**
Storage format	Adaptive Server datatype
SYBCHAR	char, chavarchar (ASCII), nchar, nvarchar
SYBTEXT	text
SYBBINARY	binary, timestamp, unichar, univarchar, varbinary
SYBIMAGE	image
SYBINT1	tinyint
SYBINT2	smallint
SYBINT4	int
SYBINT8	bigint
SYBFLT8	float
SYBREAL	real
SYBBIT	bit
SYBNUMERIC	numeric
SYBDECIMAL	decimal
SYBMONEY	money
SYBMONEY4	smallmoney
SYBDATETIME	datetime
SYBDATETIME4	smalldatetime
SYBDATE	date
SYBTIME	time
SYBUINT8	unsigned bigint
SYBUINT4	unsigned int
SYBUINT2	unsigned smallint
SYBUNITEXT	unitext
SYBFLT8	double

Data written to a host file in its native format preserves all of its precision. datetime and float values preserve all of their precision, even when they are converted to character format. Adaptive Server stores money values to a precision of one ten-thousandth of a monetary unit. However, when money values are converted to character format, their character format values are recorded only to the nearest two places.

See Chapter 1, “System and User-Defined Datatypes” in the Reference Manual: Building Blocks for descriptions and appropriate uses of Adaptive Server datatypes.

Prefix length

Prefix length indicates the number of bytes in the field length prefix. The prefix length is a 0-, 1-, 2-, or 4-byte unsigned integer value embedded in the host data file that specifies the actual length of data contained in the field. Some fields may have a length prefix while others do not.

Table 4-8 shows the allowable prefix length values.

**Table 4-8: Allowable prefix length values**
Length (in bytes)	Range
0	No prefix
1	2⁸-1; 0-255
2	2¹⁶-1; 0-65535
4	2³² -1; 0-4,294,967,295

Host file data length

Host file data length refers to the maximum number of bytes to copy for the field. To decide how much data to copy in or out, bcp uses one of:

The maximum field length
The prefix length, if any
The field terminator string, if any

If more than one method of field length specification is given, bcp chooses the one that copies the least amount of data.

Terminator

The terminator can be up to 30 bytes of characters enclosed in quotation marks (" "). The terminator designates the end of data for the host data file field.

Server column order

The server column order represents the colid (column ID) of the syscolumns column into which the host data file column is to be loaded. Together with the host file column order, this element maps host data file fields to the database table columns.