Copies a database table to or from an operating system file in a user-specified format.
bcp [[database_name.]owner.]table_name
{in | out} datafile
[-c] [-E] [-n] [-N] [-v] [-X]
[-a display_charset]
[-A size]
[-b batchsize]
[-e errfile]
[-f formatfile]
[-F firstrow]
[-I interfaces_file]
[-J client_charset]
[-K keytab_file]
[-L lastrow]
[-m maxerrors]
[-q datafile_charset]
[-r row_terminator]
[-R remote_server_principal]
[-S server]
[-t field_terminator]
[-T text_or_image_size]
[-U username]
[-V [security_options]
[-Y ]
[-z[language ]
[-Z security_mechanism]
is optional if the table being copied is in your default database or in master. Otherwise, you must specify a database name.
is optional if you or the Database Owner own the table being copied. If you do not specify an owner, bcp looks first for a table of that name owned by you, then it looks for one owned by the Database Owner. If another user owns the table, you must specify the owner name or the command fails.
is the name of the database table or view to copy.
is the direction of the copy. in indicates a copy from a file into the database table, and out indicates a copy to a file from the database table.
is the name of an operating system file.
allows you to run bcp from a terminal where the character set differs from that of the machine on which bcp is running. -a in conjunction with -J specifies the character set translation file (.xlt file) required for the conversion. Use -a without -J only if the client character set is the same as the default character set.
specifies the network packet size to use for this bcp session. For example:
bcp -A 2048
sets the packet size to 2048 bytes for this bcp session. size must be between the values of the default network packet size and maximum network packet size configuration variables, and it must be a multiple of 512.
Use larger-than-default network packet sizes to improve the performance of large bulk-copy operations.
is the number of rows per batch of data copied (the default is to copy all the rows in one batch). Batching applies only when bulk copying in; it has no effect on bulk copying out.
performs the copy operation with char as the storage type of all columns in the data file. This option does not prompt for each field; it uses char as the storage type, no prefixes, \t (tab) as the default field terminator, and \n (newline) as the default row terminator.
is the name of an error file where bcp stores any rows that it was unable to transfer from the file to the database. Error messages from the bcp program appear on your terminal. bcp creates an error file only when you specify this option.
explicitly specifies the value of a table’s IDENTITY column.
By default, when you bulk copy data into a table with an IDENTITY column, bcp assigns each row a temporary IDENTITY column value of 0. This is only effective when copying data into a table. bcp reads the value of the ID column from the data file, but ignores it and does not send it to the server; instead, as bcp inserts each row into the table, the server assigns the row a unique, sequential IDENTITY column value, beginning with the value 1. If you specify the -E flag when copying data into a table, bcp will read the value from the data file and send it to the server, which will insert these values into the table. If the number of inserted rows exceeds the maximum possible IDENTITY column value, Adaptive Server returns an error.
The -E option has no effect when copying data out, in other words, the ID column is copied to the data file (unless the -N option is used).
is the full path name of a file with stored responses from a previous use of bcp on the same table. After you answer bcp’s format questions, it prompts you to save your answers in a format file; creation of the format file is optional. The default file name is bcp.fmt. The bcp program can refer to a format file when copying data, so that you do not have to duplicate your previous format responses interactively. Use this option only when you previously created a format file that you want to use now for a copy in or out. If this option is not used, bcp queries you for format information interactively.
is the number of the first row to copy (default is the first row).
specifies the name and location of the interfaces file to search when connecting to Adaptive Server. If you do not specify -I, bcp looks for an interfaces file located in the directory specified by the SYBASE environment variable.
specifies the character set to use on the client. bcp uses a filter to convert input between client_charset and the Adaptive Server character set.
-J client_charset requests that Adaptive Server convert to and from client_charset, the character set used on the client.
-J with no argument sets character-set conversion to NULL. No conversion takes place. Use this if the client and server use the same character set.
Omitting -J sets the character set to a default for the platform which may not necessarily be the character set that the client is using. Table A-1 lists platform defaults.
Platform |
Default character set |
|---|---|
Sun, Digital, Pyramid, RS6000/AIX, others |
iso_1 |
HP |
roman8 |
can be used only with DCE security. It specifies a DCE keytab file that contains the security key for the user name specified with -U option. Keytab files can be created with the DCE dcecp utility. See your DCE documentation for more information.
If the -K option is not supplied, the bcp user must be logged in to DCE with the same user name as specified with the -U option.
is the number of the last row to copy (default is the last row).
is the maximum number of errors permitted before bcp aborts the copy. bcp throws out each row that it cannot build, counting it as one error. If you do not include this option, bcp uses a default value of 10.
performs the copy operation using native (operating system) formats. This option does not prompt for each field. Files in native data format are usually not human-readable.
WARNING! Do not use bcp in native format to recover data, salvage data, or resolve an emergency situation. Do not use bcp in native format to transport data between different hardware platforms, different operating systems, or different major versions of Adaptive Server. Using bcp in native format can create flat files that cannot be reloaded into Adaptive Server, and it may be impossible to recover the data. If you are unable to rerun bcp in character format (for example, table truncated/dropped, hardware damage, database dropped), the data will be unrecoverable.
skips the IDENTITY column. Use this option when copying data in if your host data file does not include a place holder for the IDENTITY column values, or when copying data out and you do not want to include the IDENTITY column information in the host file.
You cannot use both -N and -E options when copying in data.
specifies an Adaptive Server password. This option is ignored if -V is used.
allows you to run bcp to copy character data to or from a file system that uses a character set different from the client character set. -q in conjunction with -J specifies the character set translation file (.xlt file) required for the conversion.
In Japanese language environments, the -q flag translates Hankaku Katakana (half-width characters) into Zenkaku Katakana (full-width characters). Use with the argument “zenkaku” and with the -J flag to indicate the client’s Japanese character set (sjis or eucjis). The zenkaku.xlt file was designed to translate only from terminal display to Adaptive Server, not from Adaptive Server to the terminal.
The ascii_7 character set is compatible with
all character sets. If either Adaptive Server’s or the
client’s character set is set to ascii_7, any
7-bit ASCII character is allowed to pass between client and server
unaltered. Other characters produce conversion errors. Character
set conversion issues are covered more thoroughly in the System
Administration Guide.
specifies the default row terminator.
specifies the principal name for the server. By default, a server’s principal name matches the server’s network name (which is specified with the -S option or the DSQUERY environment variable). The -R option must be used when the server’s principal name and network name are not the same.
specifies the name of the Adaptive Server to connect to. If you specify -S with no argument, bcp uses the server that your DSQUERY environment value specifies.
specifies the default field terminator.
allows you to specify, in bytes, the maximum length of text or image data that Adaptive Server sends. The default is 32K. If a text or image field is larger than the value of -T or the default, bcp does not send the overflow.
specifies an Adaptive Server login name. If you do not specify username, bcp uses the current user’s operating system login name.
specifies network-based user authentication. With this option, the user must log in to the network’s security system before running the utility. In this case, users must supply their network user name with the -U option; any password supplied with the -P option is ignored.
-V can be followed by a security_options string of key-letter options to enable additional security services. These key letters are:
c – Enable data confidentiality service
i – Enable data integrity service
m – Enable mutual authentication for connection establishment
o – Enable data origin stamping service
r – Enable data replay detection
q – Enable out-of-sequence detection
reports the current version and copyright message of the bcp program.
specifies that, in this connection to the server, the application initiate the login with client-side password encryption. bcp (the client) specifies to the server that password encryption is desired. The server sends back an encryption key, which bcp uses to encrypt your password, and the server uses the key to authenticate your password when it arrives.
specifies that the character-set conversion is disabled in the server, and is performed by bcp on the client side when using bcp IN.
All character-set conversion is done in the server during bcp
OUT.
is the official name of an alternate language that the server uses to display bcp prompts and messages. Without the -z flag, bcp uses the server’s default language. Add languages to an Adaptive Server at installation, or afterwards with the utility langinstall or the stored procedure sp_addlanguage.
specifies the name of a security mechanism to use on the connection.
Security mechanism names are defined in the $SYBASE/install/libtcl.cfg configuration file. If no security_mechanism name is supplied, the default mechanism is used. For more information on security mechanism names, see the description of the libtcl.cfg file in the Open Client and Open Server Configuration Guide for UNIX.
In the following example, the -c option copies data out of the publishers table in character format (using char for all fields). The -t field_terminator option ends each field with a comma, and the -r row_terminator option ends each line with a Return. bcp prompts only for a password. The first backslash before the final “r” escapes the second so that one backslash prints.
bcp pubs2..publishers out pub_out -c -t , -r \\r
In the following example, bcp copies data from the publishers table to a file named pub_out for later reloading into Adaptive Server. Pressing Return accepts the defaults that the prompts specify. The same prompts appear when copying data into the publishers table.
bcp pubs2..publishers out pub_out
Password:
Enter the file storage type of field pub_id [char]:
Enter prefix length of field pub_id [0]:
Enter length of field pub_id [4]:
Enter field terminator [none]:
Enter the file storage type of field pub_name [char]:
Enter prefix length of field pub_name [1]:
Enter field terminator [none]:
Enter the file storage type of field city [char]:
Enter prefix length of field city [1]:
Enter field terminator [none]:
Enter the file storage type of field state [char]:
Enter prefix length of field state [1]:
Enter field terminator [none]:
Do you want to save this format information in a
file? [Y] y
Host filename [bcp.fmt]: pub_form
Starting copy...
3 rows copied.
Clock Time (ms.): total = 300 Avg = 1 (300.00 rows
per sec.)
To copy this data back into Adaptive Server using the saved format file, pub_form, use the following command:
bcp pubs2..publishers in pub_out -f pub_form
To see a list of possible datatypes, enter “?” at the prompt:
Enter the file storage type of field ’pub_id’
[’char’]:?
Invalid column type. Valid types are:
<cr>: same type as Adaptive Server column.
c : char
T : text
i : int
s : smallint
t : tinyint
f : float
m : money
b : bit
d : datetime
x : binary
I : image
D : smalldatetime
r : real
M : smallmoney
n : numeric
e : decimal
Enter the single letter exactly as it appears above.
The following example copies a data file created with a character set used on a VT200 terminal into the pubs2.publishers table. The -q flag translates it. The -z flag displays bcp messages in French.
bcp pubs2..publishers in vt200_data -J iso_1 -q vt200 -z french
The following example specifies that Adaptive Server send 40K of text or image data using a packet size of 4096:
bcp publishers out -T 40960 -A 4096
If there is an external Sybase configuration file, add
this section to enable bcp: [BCP]
bcp for System 11 is built with Client-Library.
The bcp user interface is unchanged except for the following:
New command-line options have been added to enable network-based security services on the connection as follows:
-K keytab_file -R remote_server_principal -V security_options -Z security_mechanism
The -y sybase_directory option is ignored.
Error message format is different than previous versions of bcp. If you have scripts that perform routines based on the values of these messages you may need to re-write them, for example:
The display message that indicates the number of rows transferred has been changed. During a session, this version of bcp periodically reports a running total of rows transferred. This message replaces the “1000 rows transferred” message displayed by the previous bcp.
To use a previous version of bcp,
you must set the CS_BEHAVIOR property in the [bcp] section
of the ocs.cfg file:
[bcp]
CS_BEHAVIOR = CS_BEHAVIOR_100
If CS_BEHAVIOR is not set to CS_BEHAVIOR_100, you can use functionality for bcp 11.1 and later.
bcp provides a convenient, high-speed method for transferring data between a database table or view and an operating system file. It is capable of reading or writing files in a wide variety of formats. When copying in from a file, bcp inserts data to an existing database table; when copying out to a file, bcp overwrites any previous contents of the file.
Upon completion, bcp informs you of the number of rows of data successfully copied, the total time the copy took, the average amount of time in milliseconds that it took to copy one row, and the number of rows copied per second.
The bcp utility does not insert any row that contains an entry exceeding the character length of the corresponding target table column. For example, bcp does not insert a row with a field of 300 bytes into a table with a character column length of 256 bytes. Instead, bcp reports a conversion error, for example:
cs_convert: cslib user api layer: common library error: The result is truncated because the conversion/operation resulted in overflow
and skips the row. bcp does not insert truncated data into the table.
To keep track of data that violate length requirements, run bcp with the -e log-file name option. bcp records the row and column number of the rejected data, the error message, and the data in the log file you specify.
The bcp program is optimized to load data into tables that do not have indexes or triggers associated with them. It loads data into tables without indexes or triggers at the fastest possible speed, with a minimum of logging. Page allocations are logged, but the insertion of rows is not.
When you copy data into a table that has one or more indexes or triggers, a slower version of bcp is automatically used, which logs row inserts. This includes indexes implicitly created using the unique integrity constraint of a create table statement. However, bcp does not enforce the other integrity constraints defined for a table.
Because the fast version of bcp inserts data without logging it, the System Administrator or Database Owner must first set the system procedure sp_dboption, “DB”, true. If the option is not true, and you try to copy data into a table that has no indexes or triggers, Adaptive Server generates an error message. You do not need to set this option to copy data out to a file, or to copy data into a table that contains indexes or triggers.
Because bcp logs inserts into a table
that has indexes or triggers, the log can grow very large. You can
truncate the log with dump
transaction after the bulk copy completes, after you have
backed up your database with dump database.
While the select into/bulkcopy option is “on,” you cannot dump the transaction log. Issuing dump transaction produces an error message instructing you to use dump database instead.
WARNING! Be certain that you dump your database before you turn off the select into/bulkcopy flag. If you insert unlogged data into your database, and then perform a dump transaction before you perform a dump database, you will not be able to recover your data.
Unlogged bcp runs more slowly while a dump database is taking place.
Table A-2 shows which version bcp uses when copying in, the necessary settings for the select into/bulkcopy option, and whether the transaction log is kept and if it is dumpable.
select into/bulk copy |
||
|---|---|---|
bcp version |
on |
off |
Fast bcp (no indexes or triggers on target table) |
OK dump transaction prohibited |
bcp dump transaction prohibited |
Slow bcp (one or more indexes or triggers) |
OK dump transaction prohibited |
OK dump transaction OK |
By default, the select into/bulkcopy option is “off” in newly created databases. To change the default situation, turn this option “on” in the model database.
The performance penalty for copying data into a table
that has indexes or triggers in place can be severe. If you are
copying in a very large number of rows, it may be faster to drop
all the indexes
and triggers
first with drop
index (or alter table for indexes created
as a unique constraint) and drop trigger;
set the database option; copy the data into the table; re-create
the indexes and triggers; and then dump the database. Remember to
allocate disk space for the construction of indexes and triggers
for a clustered index, about 1.2 times the amount of space needed
for the data, in addition to the space needed for the data.
When you copy data in or out using the -n (native format) or -c (character format) option, bcp only prompts you for your password, unless you supplied it with the -P option. If you do not supply either the -n, -c or -f formatfile option, bcp prompts you for information for each field in the table.
Each prompt displays a default value, in brackets, which you can accept by pressing Return. The prompts include:
The file storage type, which can be character or any valid Adaptive Server datatype
The prefix length, which is an integer indicating the length in bytes of the following data
The storage length of the data in the file for nonNULL fields
The field terminator, which can be any character string
Scale and precision for numeric and decimal dat types
The row terminator is the field terminator of the last field in the table or file.
The bracketed defaults represent reasonable values for the datatypes of the field in question. For the most efficient use of space when copying out to a file:
Use the default prompts.
Copy all data in their table datatypes.
Use prefixes as indicated.
Do not use terminator.s
Accept the default lengths.
The following table shows the defaults and possible alternate responses:
Prompt |
Default provided |
Possible responses |
|---|---|---|
File storage type |
Use database storage type for most fields except: char for varchar binary for varbinary |
char to create or read a human-readable file; any CS datatype where implicit conversion is supported. |
Prefix length |
0 for fields defined with char datatype (not storage type) and all fixed-length datatypes. 1 for most other datatypes. 2 for binary and varbinary saved as char. 4 for text and image. |
0 if no prefix is desired; defaults are recommended in all other cases. |
Storage length |
For char and varchar, use defined length. For binary and varbinary saved as char, use default. For all other datatypes, use maximum length needed to avoid truncation or data overflow. |
Default values, or greater, are recommended. |
Field or row terminator |
None. |
Up to 30 characters, or one of the following: \t tab \n newline \r carriage return \0 null terminator \ backslash |
bcp can copy data out to a file either as its native (database) datatype, or as any datatype for which implicit conversion is supported for the datatype in question. bcp copies user-defined datatypes as their base datatype or as any datatype for which implicit conversion is supported. For more information on datatype conversions, see cs_convert information in the Open Client-Client Library/C Reference Manual.
Be careful copying data in native format from different
versions of Adaptive Server because they do not always have the
same datatypes.
A prefix length is a 1-, 2-, or 4-byte integer that represents the length of each data value in bytes. It immediately precedes the data value in the host file.
Fields defined in the database as char, nchar, and binary are always padded with spaces (null bytes for binary) to the full length defined in the database. timestamp data is treated as binary(8).
If data in varchar and varbinary fields is longer than the length you specify for copy out, bcp truncates the data in the file at the specified length.
A field terminator string can be up to 30 characters long; the most common terminators are a tab (entered as “\t” and used for all columns except the last one), and a new line (entered as “\n” and used for the last field in a row). Other terminators are: “\0” (the null terminator), “\” (backslash), and “\r” (Return). When choosing a terminator, be sure that its pattern does not appear in any of your character data. For example, if you use tab terminators with a string that contains a tab, bcp could not identify which tab represents the end of the string. Because bcp always looks for the first possible terminator, in this case it would find the wrong one.
When a terminator or prefix is present, it affects the actual length of data transferred. If the length of an entry being copied out to a file is less than the storage length, it is followed immediately by the terminator, or the prefix for the next field. The entry is not padded to the full storage length (char, nchar, and binary data is returned from Adaptive Server already padded to the full length).
When copying in from a file, data is transferred until either the number of bytes indicated in the “Length” prompt has been copied or the terminator is encountered. Once a number of bytes equal to the specified length has been transferred, the rest of the data is flushed until the terminator is encountered. When no terminator is used, the table storage length is strictly observed.
The following tables show the interaction of prefix lengths, terminators, and field length on the information in the file. “P” indicates the prefix in the stored table. “T” indicates the terminator, and dashes (- -) show appended spaces. An ellipsis (...) indicates that the pattern repeats for each field. The field length is 8 for each column, and “string” represents the 6-character field each occurence.
Prefix length = 0 |
Prefix length 1,2,or 4 |
|
|---|---|---|
No Terminator |
string--string--. |
Pstring--Pstring--. |
Terminator |
string--Tstring--T. |
Pstring--TPstring--T. |
Prefix length = 0 |
Prefix length 1,2,or 4 |
|
|---|---|---|
No Terminator |
string--string--. |
PstringPstring. |
Terminator |
stringTstringT. |
PstringTPstringT. |
Note that the file storage type and length of a column do not have to be the same as the type and length of the column in the database table. (If types and formats copied in are incompatible with the structure of the database table, the copy fails.
File storage length generally indicates the maximum amount of data to be transferred for the column, excluding terminators and prefixes.
When copying data into a table, bcp observes any defaults defined for columns and user-defined datatypes. However, bcp ignores rules to load data at the fastest possible speed.
Because bcp considers any data column that can contain null values to be variable length, use either a length prefix or terminator to denote the length of each row of data.
Data written to a host file in its native format preserves all of its precision. datetime and float values preserve all of their precision even when they are converted to character format. Adaptive Server stores money values to a precision of one ten-thousandth of a monetary unit. However, when money values are converted to character format, their character format values are recorded only to the nearest two places.
Before copying data that is in character format from a file into a database table, check the datatype entry rules in the “Datatypes” section of the Adaptive Server Reference Manual. Character data that is being copied into the database with bcp must conform to those rules. Note especially that dates in the undelimited (yy)yymmdd format may result in overflow errors if the year is not specified first.
When you send host data files to sites that use terminals different from your own, inform them of the datafile_charset that you used to create the files.
Error in attempting to load a view of translation tables.
The character translation file(s) named with the -a or -q parameter is missing, or you mistyped the name(s).
