Examples of Copying Out Data Interactively

By changing the default values of the prompts to bcp, you can prepare data for use with other software.

To create a human-readable file, respond to the bcp prompts:

File storage type, enter 0.
Prefix length, enter 0.
Field length, accept the default.
Terminator – the field terminator you enter depends on the software that you plan to use.
- Choose between delimited fields or fixed-length fields. Always use \n, the newline terminator, to terminate the last field.
  
  For fixed-length fields, do not use a terminator. Each field has a fixed length, with spaces to pad the fields. Adjacent fields, where the data completely fills the first field seem to run together, since there are no field separators on each line of output. See the example below.
- For comma-delimited output, use a comma (,) as the terminator for each field. To create tabular output, use the tab character (\t).

Copy Out Data with Field Lengths

This example uses fixed-length fields to create output in the personal computer format called SDF (system data format). This format can be easily read or produced by other software.

bcp pubs2..sales out sal_out

The results as stored in the sal_out file are:

5023      AB-123-DEF-425-1Z3    Oct 31 1985 12:00AM
5023      AB-872-DEF-732-2Z1    Nov 6 1985 12:00AM
5023      AX-532-FED-452-2Z7    Dec 1 1990 12:00AM
5023      BS-345-DSE-860-1F2    Dec 12 1986 12:00AM
5023      GH-542-NAD-713-9F9    Mar 15 1987 12:00AM
5023      NF-123-ADS-642-9G3    Jul 18 1987 12:00AM
5023      XS-135-DER-432-8J2    Mar 21 1991 12:00AM
5023      ZA-000-ASD-324-4D1    Jul 27 1988 12:00AM
5023      ZD-123-DFG-752-9G8    Mar 21 1991 12:00AM
5023      ZS-645-CAT-415-1B2    Mar 21 1991 12:00AM
5023      ZZ-999-ZZZ-999-0A0    Mar 21 1991 12:00AM
6380      234518                Sep 30 1987 12:00AM
6380      342157                Dec 13 1985 12:00AM
6380      356921                Feb 17 1991 12:00AM
7066      BA27618               Oct 12 1985 12:00AM
7066      BA52498               Oct 27 1987 12:00AM
7066      BA71224               Aug 5 1988 12:00AM
7067      NB-1.142              Jan 2 1987 12:00AM
7067      NB-3.142              Jun 13 1990 12:00AM
7131      Asoap132              Nov 16 1986 12:00AM
7131      Asoap432              Dec 20 1990 12:00AM
7131      Fsoap867              Sep 8 1987 12:00AM
7896      124152                Aug 14 1986 12:00AM
7896      234518                Feb 14 1991 12:00AM
8042      12-F-9                Jul 13 1986 12:00AM
8042      13-E-7                May 23 1989 12:00AM
8042      13-J-9                Jan 13 1988 12:00AM
8042      55-V-7                Mar 20 1991 12:00AM
8042      91-A-7                Mar 20 1991 12:00AM
8042      91-V-7                Mar 20 1991 12:00AM

The contents of the sal_fmt format file are:

10.0
3
1    SYBCHAR 04  "" 1  stor_id
2    SYBCHAR 020 "" 2  ord_num
3    SYBCHAR 026 "" 3  date

Comma-delimited, Newline-delimited with Format File

In this example, bcp copies data interactively from the publishers table to a file:

bcp pubs2..publishers out pub_out

The results as stored in the pub_out file are:

0736,New Age Books,Boston,MA
0877,Binnet  & Hardley,Washington,DC
1389,Algodata Infosystems,Berkeley,CA

The contents of the pub_fmt format file are:

10.0
4
1    SYBCHAR 0 4   ","   1  pub_id
2    SYBCHAR 0 40  ","   2  pub_name
3    SYBCHAR 0 20  ","   3  city
4    SYBCHAR 0 2   "\n"  4  state

This example creates:

An output file with commas between all fields in a row and a newline terminator at the end of each row
A format file (pub_fmt) that you can use later to copy the same or similar data back into the SAP ASE server

Tab-delimited with Format File

This example creates tab-delimited output from the table pubs2..publishers in the pub_out file:

bcp pubs2..publishers out pub_out

The results as stored in the pub_out file are:

0736    New Age Books           Boston        MA
0877    Binnet & Hardley        Washington    DC
1389    Algodata Infosystems    Berkeley      CA

The contents of the pub_fmt format file are:

10.0
4
1    SYBCHAR 04   "\t"  1 pub_id
2    SYBCHAR 040  "\t"  2 pub_name
3    SYBCHAR 020  "\t"  3 city
4    SYBCHAR 02   "\n"  4 state