Home

	Expand Search

Utility Guide

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

Using the bcp options

Using the default formats

Using the default formats

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.

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.

Warning!

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 (-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