Choose terminators with patterns that do not appear in any of the data.
For example, using a tab terminator with a string of data that also contains a tab creates ambiguity: which tab represents the end of the string? bcp always looks for the first possible terminator, which in this case would be incorrect, since the first tab it would encounter would be part of the data string.
Data in native format can also conflict with terminators. If the values of these integers are not strictly limited in a column containing a 4-byte integer in native format, it is impossible to choose a terminator that is guaranteed not to appear inside the data. Use bcp’s native format option for data in native format.
A field terminator string can be up to 30 characters long. The most common terminators are a tab (entered as \t and used for all columns except the last one), and a newline (entered as \n and used for the last field in a row). Other terminators are: \0 (the null terminator), \ (backslash), and \r (Return). When choosing a terminator, its pattern cannot appear in any of your character data, since bcp always looks for the first possible terminator.
For example, if you used tab terminators with a string that contained a tab, bcp cannot identify which tab represents the end of the string. bcp always looks for the first possible terminator, so, in this example it would find the wrong one.
A terminator or prefix affects the actual length of data transferred:
When a terminator or prefix is present, it affects the length of data transferred. If the length of an entry being copied out to a file is less than the storage length, it is immediately followed by the terminator or the prefix for the next field. The entry is not padded to the full storage length (char, nchar, and binary data is returned from Adaptive Server already padded to the full length).
When bcp is copying in from a file, data is transferred until either the number of bytes indicated in the “Length” prompt has been copied or the terminator is encountered. Once the number of bytes equal to the specified length has been transferred, the rest of the data is flushed until the terminator is encountered. When no terminator is used, the table storage length is strictly observed.
Fields stored as char (except char, nchar, and binary fields) instead of their database datatypes take less file storage space with the default length and prefix or a terminator. bcp can use either a terminator or a prefix to determine the most efficient use of storage space. bcp suggests the maximum amount of storage space required for each field as the default. For char or varchar data, bcp accepts any length.
The following two tables show the interaction of prefix lengths, terminators, and field length on the information in the file. “P” indicates the prefix in the stored table; “T” indicates the terminator; and dashes, (--) show appended spaces. An ellipsis (…) indicates that the pattern repeats for each field. The field length is 8 bytes for each column; “string” represents the 6-character field each time.