Examples of Copying In Data Interactively

To copy in data successfully to a table from a file, know what the terminators in the file are or what the field lengths are and specify them when you use bcp.

The following examples show how to copy data in, either with fixed field lengths or with delimiters, using bcp with or without a format file.

Copy In Data with Field Lengths

In this example, bcp copies data from the salesnew file into the pubs2..sales table.

In the salesnew file are three fields: the first is 4 characters long, the second is 20, and the third is 26 characters long. Each row ends with a newline terminator (\n):

5023ZS-731-AAB-780-2B9       May 24 1993 12:00:00:000AM
5023XC-362-CFB-387-3Z5       May 24 1993 12:00:00:000AM
6380837206                   May 24 1993 12:00:00:000AM
6380838441                   May 24 1993 12:00:00:000AM

Use the following command to copy in the data interactively from salesnew:

bcp pubs2..sales in salesnew

The system responds to the bcp command:

Password:
Enter the file storage type of field stor_id [char]:
Enter prefix-length of field stor_id [0]:
Enter length of field stor_id [4]:
Enter field terminator [none]:
Enter the file storage type of field ord_num [char]:
Enter prefix-length of field ord_num [1]: 0
Enter length of field ord_num [20]:
Enter field terminator [none]:
Enter the file storage type of field date [datetime]: char
Enter prefix-length of field date [1]: 0
Enter length of field date [26]:
Enter field terminator [none]: \n
Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: salesin_fmt
Starting copy...
4 rows copied.
Clock Time (ms.): total = 1 Avg = 0 (116000.00 rows per sec.)

When you log in to the SAP ASE server and access sales, you see the following data from salesnew appended to the table:

select * from sales
stor_id  ord_num               date
------- -------------------- -------------------------
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
(34 rows affected)

Since there is a unique clustered index on the stor_id and ord_num columns of sales, the new rows were sorted in order.

A conflict or violation can affect the copy process:

Had there been any violations of the unique index on the columns in the data being copied from the file, bcp would have discarded the entire batch in which the violating row was encountered.

A batch size of 1 evaluates each row individually, but loads more slowly and creates a separate data page for each row during a fast or fast-logged bcp session.
If the types copied in are incompatible with the database types, the entire copy fails.

Copy In Data with Delimiters

In this example, bcp copies data from the file newpubs into the table pubs2..publishers. In the newpubs file, each field in a row ends with a tab character (\t) and each row ends with a newline terminator (\n):

1111  Stone Age Books             Boston       MA
2222  Harley & Davidson           Washington   DC
3333  Infodata Algosystems        Berkeley     CA

Since newpubs contains all character data, use the character command-line flag and specify the terminators with command line options:

In UNIX platforms:

bcp pubs2..publishers in newpubs -c -t\\t -r\\n

In Windows:

bcp pubs2..publishers in newpubs -c -t\t -r\n

Copy In Data with a Format File

To copy data back into the SAP ASE server using the saved pub_fmt format file, run:

bcp pubs2..publishers in pub_out -fpub_fmt

Use the pub_fmt file to copy any data with the same format into the SAP ASE server. If you have a similar data file with different delimiters, change the delimiters in the format file.

Similarly, edit the format file to reflect any changes to the field lengths, as long as all fields have the same length. For example, the moresales file contains:

804213-L-9 Jan 21 1993 12:00AM
804255-N-8 Mar 12 1993 12:00AM
804291-T-4 Mar 23 1993 12:00AM
804291-W-9 Mar 23 1993 12:00AM

Edit the sal_fmt format file to read:

10.0
3
1    SYBCHAR 0 4 "" 1 stor_id
2    SYBCHAR 0 7 "" 2 ord_num
3    SYBCHAR 0 21 "\n" 3 date

Then enter:

For UNIX platforms:
```
bcp pubs2..sales in moresales -fsal_fmt
```
For Windows:
```
bcp pubs2..sales in moresale -fsal_fmt
```

The system responds:

Starting copy...
4 rows copied.
 Clock Time (ms.): total = 1 Avg = 0 (116000.00 rows 
per sec.)