Home

	Expand Search

Utility Guide

Chapter 3 Using bcp to Transfer Data to and from Adaptive Server

Changing the defaults: interactive bcp

Field length

Field length

In almost all cases, use the bcp default value for the storage length while copying data out.

The terms "length" and "storage length" in this section refer to the operating system file, not to Adaptive Server field lengths.

• If you are creating a file to reload into Adaptive Server, the default prefixes and length keep the storage space needed to a minimum.

• If you are creating a human-readable file, the default length prevents the truncation of data or the creation of overflow errors that cause bcp to fail.

Because you can change the default length by supplying another value, you must be familiar with the data to transfer. If you are copying character data in from other software, examine the source file carefully before choosing length values.

If the storage type is noncharacter, bcp stores the data in the operating system's native data representation and does not prompt for a length.

When bcp converts noncharacter data to character storage, it suggests a default field length that is large enough to store the data without truncating datetime data or causing an overflow of numeric data.

• The default lengths are the number of bytes needed to display the longest value for the Adaptive Server datatype. Table 3-5 lists the default field lengths for data conversion to character storage.

  Default field lengths for noncharacter to character datatypes

  Datatype	Default size
  int	12 bytes
  smallint	6 bytes
  tinyint	3 bytes
  float	25 bytes
  money	24 bytes
  bit	1 byte
  datetime	26 bytes
  smalldatetime	26 bytes
  real	25 bytes
  smallmoney	24 bytes

• If you specify a field length that is too short for numeric data when copying data out, bcp prints an overflow message and does not copy the data.

• The default length for binary and varbinary fields is twice the length defined for the column, since each byte of the field requires 2 bytes of file storage.

• If you accept the default storage length, the actual amount of storage space allocated depends on whether or not you specify a prefix length and terminators.

  • If you specify a prefix length of 1, 2, or 4, bcp uses a storage space of the actual length of the data, plus the length of the prefix, plus any terminators.

  • If you specify a prefix length of 0 and no terminator, bcp allocates the maximum amount of space shown in the prompt, which is the maximum space that may be needed for the datatype in question. In other words, bcp treats the field as if it were fixed length to determine where one field ends and the next begins.

    For example, if the field is defined as varchar(30), bcp uses 30 bytes for each value, even if some of the values are only 1 character long.

• Fields defined in the database as char, nchar, and binary, and those that do not permit null values, are always padded with spaces (null bytes for binary) to the full length defined in the database. timestamp data is treated as binary(8).

• If data in the varchar and varbinary fields is longer than the length specified for copy out, bcp silently truncates the data in the file at the specified length.

• bcp does not know how large any one data value will be before copying all the data, so it always pads char datatypes to their full specified length.

• The file storage type and length of a column do not have to be the same as the type and length of the column in the database table. If the types and formats copied in are incompatible with the structure of the database table, the copy fails.

• File storage length generally indicates the maximum amount of data that can be transferred for the column, excluding terminators and/or prefixes.

• When copying data into a table, bcp observes any defaults defined for columns and user-defined datatypes. However, bcp ignores rules in order to load data at the fastest possible speed.

• bcp considers any data column that can contain a null value to be variable length, so use either a length prefix or a terminator to denote the length of each row of data.

• The file storage type and length of a column need not be the same as the type and length of the column in the database table. (If types and formats copied in are incompatible with the structure of the database table, the copy fails.)