Utility Guide

Home

Utility Guide

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

Examples: 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 as follows:

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
```
  ).

Copying out data with field lengths

The following 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.

For information about format files, see "Using format files".

bcp pubs2..sales out sal_out

The results as stored in the sal_out file are as follows:

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                        J        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 as follows:

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

For information about format files, see "Using format files".

Copying out data with delimiters

In the following examples, bcp copies data interactively from the publishers table to a file.

For information about format files, see "Using format files".

Comma-delimited, newline-delimited with format file

The first example creates an output file with commas between all fields in a row and a newline terminator at the end of each row. This example creates a format file (pub_fmt) that you can use later to copy the same or similar data back into Adaptive Server.

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

Tab-delimited with format file

Similarly, the following 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


	Expand Search