|Chapter 3 Using bcp to Transfer Data to and from Adaptive Server|
|Using the bcp options|
The information in this section clarifies some of the more complex options of the bcp syntax. For a complete description of the syntax, see bcp.
bcp provides two command-line options that create files with frequently used default formats. These options provide the easiest way to copy data in and out from Adaptive Server.
The -n option uses "native" (operating system) formats.
The -c option uses "character" (char datatype) for all columns. This datatype supplies tabs between fields on a row and a newline terminator, such as a carriage return, at the end of each row.
When you use the native or character options, bcp operates noninteractively and only asks you for your Adaptive Server password.Native format
The -n option creates files using native (operating system-specific) formats. Native formats usually create a more compact operating system file. For example, the following command copies the publishers table to the file called pub_out, using native data format:
bcp pubs2..publishers out pub_out -n
Here are the contents of pub_out:
0736^MNew Age Books^FBoston^BMA0877^PBinnet & Hardley^J Washington^BDC1389^TAlgodata Infosystems^HBerkeley^BCA
bcp prefixed each field, except the pub_id, which is a char(4) datatype, with an ASCII character equivalent to the length of the data in the field. For example, "New Age Books" is 13 characters long, and ^M (Ctrl-m) is ASCII 13.
All the table data stored in the pub_out file is char or varchar data, so it is human-readable. In a table with numeric data, bcp writes the information to the file in the operating system's data representation format, which may not be human-readable.
bcp can copy data out to a file either as its native (database) datatype or as any datatype for which implicit conversion is supported for the datatype in question. bcp copies user-defined datatypes as their base datatype or as any datatype for which implicit conversion is supported. For more information on datatype conversions, see dbconvert in the Open Client DB-Library/C Reference Manual or the Sybase Adaptive Server Enterprise Reference Manual.
The bcp utility does not support copying data in native format from different operating systems; for example, copying from NT to UNIX. Use the -c flag if you need to use bcp to copy files from one operating system to another.
Do not use row terminator (-t) or field terminator (-r) parameters with bcp in native format. Results are unpredictable and data may be corrupted.Character format
Character format (-c) uses the char datatype for all columns. It inserts tabs between fields in each row and a newline terminator at the end of each row.
For example, the following command copies out the data from the publishers table in character format to the file pub_out:
bcp pubs2..publishers out pub_out -c
The command produces the following bcp output:
0736 New Age Books Boston MA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA
Terminators are the characters that separate data fields (field terminators). The row terminator is the field terminator of the last field in the table or file. Use the -tfield_terminator and -rrow_terminator command line options with the character format option (-c) to change the terminators from the command line.
The following example uses the comma (
,) as the field terminator and return (\r) as the row terminator. In UNIX platforms:
bcp pubs2..publishers out pub_out -c -t , -r \\r
Remember to "escape" the backslash, if necessary, for your operating system command shell.
In Windows NT:
bcp pubs2..publishers out pub_out -c -t , -r \r
This bcp command line produces the following information:
0736,New Age Books,Boston,MA 0877,Binnet & Hardley,Washington,DC 1389,Algodata Infosystems,Berkeley,CA
You can use the -t and -r options to change the default terminators without including the character option (-c).