| |
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 |
|
| Copying 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), 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.
|
|