| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Utility Guide |
![[-]](/dwicons/i_colpse.gif)
| Chapter 3 Using bcp to Transfer Data to and from Adaptive Server |
|
| Examples: copying in data interactively |
To copy in data successfully to a table from a file, you must 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.
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), as follows:
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 as follows:
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 Adaptive 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 bcp session.
If the types copied in are incompatible with the database types, the entire copy fails.
In the following 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), as follows:
1111 Stone Age Books Boston MA 2222 Harley & Davidson Washington DC 3333 Infodata Algosystems Berkeley CA
Since newpubs contains all character data, you can 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 NT:
bcp pubs2..publishers in newpubs -c -t\t -r\n
To copy data back into Adaptive Server using the saved pub_fmt format file, run the following command:
bcp pubs2..publishers in pub_out -fpub_fmt
You can use the pub_fmt file to copy any data with the same format into Adaptive Server. If you have a similar data file with different delimiters, you can change the delimiters in the format file.
Similarly, you can 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 as follows:
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 the following command:
In UNIX platforms:
bcp pubs2..sales in moresales -fsal_fmt
In Windows NT:
bcp pubs2..sales in moresale -fsal_fmt
The system responds as follows:
Starting copy...
4 rows copied. Clock Time (ms.): total = 1 Avg = 0 (116000.00 rows per sec.)
|
|