Copies a database table to or from an operating system file in a user-specified format. bcp is located in $SYBASE/OCS-12_5/bin.
In Windows NT, the utility is bcp.exe,
and is located in %SYBASE%\OCS-12_5\bin.
bcp [[database_name.]owner.]table_name [:slice_number] {in | out} datafile
[-m maxerrors]
[-f formatfile]
[-e errfile]
[-F firstrow]
[-L lastrow]
[-b batchsize]
[-n]
[-c]
[-t field_terminator]
[-r row_terminator]
-U username
[-P password]
[-I interfaces_file]
[-S server]
[-a display_charset]
[-q datafile_charset]
[-z language]
[-A packet_size]
[-J client_charset]
[-T text_or_image_size]
[-E]
[-g id_start_value]
[-N]
[-X]
[-M LabelName LabelValue]
[-labeled]
[-K keytab_file]
[-R remote_server_principal]
[-V [security_options]]
[-Z security_mechanism]
[-Q]
Or
bcp -v
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 owns the table being copied. If you do not specify an owner, bcp looks first for a table of that name that you own, and then 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 view you are copying out.
is the name of the database table to copy. The table name cannot be a Transact-SQL reserved word.
Partition number partition_number does not exist in table table_name.
is the number of the slice of the database table to copy.
is the identifier of the partition into which to copy.
is the direction of the copy. in indicates a copy from a file into the database table; out indicates a copy to a file from the database table or view.
is the full path name of an operating system file. The path name can be from 1 to 255 characters in length.
is the maximum number of nonfatal errors permitted before bcp aborts the copy. bcp discards each row that it cannot insert (due to a data conversion error, or an attempt to insert a null value into a column that does not allow them), counting each rejected row as one error. If you do not include this parameter, bcp uses a default value of 10.
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 you are copying data so that you do not have to duplicate your previous format responses interactively. Use the -f parameter only if you previously created a format file that you want to use now for a copy in or copy out. If you do not specify this parameter, bcp interactively queries you for format information.
is the full path 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 bcp appear on your terminal. bcp creates an error file only when you specify this parameter.
is the number of the first row to copy from an input file (default is the first row).
Avoid using the -F option when performing heavy-duty, multi-process copying, as it causes bcp to generally spend more effort to run, and does not provide you with a faster process. Instead, use -F for single-process, ad-hoc copying.
is the number of the last row to copy from an input file (default is the last row).
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 you are bulk copying in; it has no effect on bulk copying out. The smallest number bcp accepts for batchsize is 10. If you specify a smaller number, bcp automatically changes the value to 10.
Setting
the batch size to 10 causes Adaptive Server to allocate one data page
to 10 rows copied in. This option only applies to fast bcp,
and is only useful in locating corrupt rows of data.
performs the copy operation using native (operating system) formats. Specifying the -n parameter means bcp will not prompt for each field. Files in native data format are not human-readable.
WARNING! Do not use bcp in native format for data recovery or salvage or to resolve an emergency situation. Do not use bcp in native format to transport data between different hardware platforms, different operating systems, or different major releases of Adaptive Server. Do not use field terminators (-t) or row terminators (-r) with bcp in native format. Results are unpredictable and data may become corrupted. 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 cannot rerun bcp in character format (for example, a table was truncated or dropped, hardware damage occurred, a database was dropped, and so on) the data is unrecoverable.
performs the copy operation with char datatype
as the default storage type of all columns in the data file. Use
this format if you are sharing data between platforms. This parameter
does not prompt for each field; it uses char as
the default storage type, no prefixes, \t (tab)
as the default field terminator, and \n (new
line) as the default row terminator.
specifies the default field terminator.
specifies the row terminator.
WARNING! Do not use -t or -r parameters with bcp in native format. Results are unpredictable and data may become corrupted.
When specifying terminators from the command line with the -t or -r parameter, you must escape characters that have special significance to the UNIX operating system (or the command prompt shell for Windows NT). See the examples for bcp for more information. Either place a backslash in front of the special character or enclose it in quotes. This is not necessary when bcp prompts you (interactive mode).
specifies an Adaptive Server login name.
specifies an Adaptive Server password. If you do not specify -Ppassword, bcp prompts for a password. You can leave out the -P flag if your password is NULL.
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 (sql.ini in Windows NT) located in the directory specified by the SYBASE environment variable (ini directory in Windows NT).
specifies the name of the Adaptive Server to which to connect. If you specify -S with no argument, bcp uses the server specified by the DSQUERY environment variable.
allows you to run bcp from a terminal where the character set differs from that of the machine on which bcp is running. Use -a in conjunction with -J to specify 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.
The following error message appears if the character translation file(s) named with the -a parameter is missing, or you mistype the name(s):
Error in attempting to determine the size of a pair of translation tables.:’stat’ utility failed.
runs bcp to copy character data to or from a file system that uses a character set different from the client character set. Use -q in conjunction with -J to specify 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 translates only from the terminal to Adaptive Server, not from Adaptive Server to the terminal.
The following error message appears if the character translation file(s) named with the -q parameter is missing, or you mistype the name(s):
Error in attempting to determine the size of a pair of translation tables.:’stat’ utility failed.
The ascii_7 character set is compatible with
all character sets. If either the Adaptive Server character set
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.
is the official name of an alternate language the server uses to display bcp prompts and messages. Without the -z flag, bcp uses the server’s default language.
You can add languages to an Adaptive Server during installation or afterwards, using either the langinstall utility (or langinst in Windows NT) or the sp_addlanguage stored procedure.
The following error message appears if an incorrect or unrecognized language is named with the -z parameter:
Unrecognized localization object. Using default value 'us_english'. Starting copy... => warning.
displays the version number of bcp and a copyright message and returns to the operating system.
specifies the network packet size to use for this bcp session. For example:
bcp pubs2..titles out table_out -A 2048
sets the packet size to 2048 bytes for this bcp session. packet_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 network packet sizes larger than the default to improve the performance of large bulk-copy operations.
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 6-2 lists platform defaults.
Platform |
Default Character Set |
|---|---|
Sun Solaris, Digital UNIX, NCR, RS/6000 |
iso_1 |
HP-UX |
roman8 |
OS/2, Novell NetWare 386 |
cp850 |
Macintosh |
mac |
The following error message appears if an incorrect or unrecognized character set is named with the -J parameter:
Unrecognized localization object. Using default value 'iso_1'. Starting copy... => warning.
For more information about character sets and associated flags, see the System Administration Guide.
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 an image field is larger than the value of -T or the default, bcp does not send the overflow.
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 effective only when copying data into a table. bcp reads the value of the ID column from the data file, but 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 reads the value from the data file and sends it to the server which inserts the value into the table. If the number of rows inserted exceeds the maximum possible IDENTITY column value, Adaptive Server returns an error.
The -E parameter has no effect when you are bulk copying data out. Adaptive Server copies the ID column to the data file, unless you use the -N parameter.
You cannot use the -E and -g flags together.
specifies the value of the IDENTITY column to use as a starting point for copying data in.
You cannot use the -g and -E flags together.
skips the IDENTITY column. Use this parameter when copying data in if your host data file does not include a placeholder for the IDENTITY column values, or when copying data out, if you do not want to include the IDENTITY column information in the host file.
You cannot use both -N and -E parameters when copying data in.
specifies that, in this connection to the server, the application initiates 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.
If bcp crashes, the system creates a core file that contains your password. If you did not use the encryption option, the password appears in plain text in the file. If you used the encryption option, your password is not readable.
(secure SQL server only) enables multilevel users to set the session labels for the bulk-copy. Valid values for label_name are:
curread (current read level) is the initial level of data that you can read during this session. curread must dominate curwrite.
curwrite (current write level) is the initial sensitivity level that will be applied to any data that you write during this session.
maxread (maximum read level) is the maximum level at which you can read data. This is the upper bound to which you as a multilevel user can set your curread during the session. maxread must dominate maxwrite.
maxwrite (maximum write level) is the maximum level at which you can write data. This is the upper bound to which you as a multilevel user can set your curwrite during a session. maxwrite must dominate minwrite and curwrite.
minwrite (minimum write level) is the minimum level at which you can write data. This is the lower bound to which you as a multilevel user can set curwrite during a session. minwrite must be dominated by maxwrite and curwrite.
label_value is the actual value of the label, expressed in the human-readable format used on your system (for example, “Company Confidential Personnel”).
(secure SQL server only) indicates that the data you are importing already has labels in the first field of every record.
For exporting data this option indicates that you want the sensitivity label of every row to be copied out as the first field.
specifies the path to the keytab file used for authentication in DCE.
specifies the principal name for the server as defined to the security mechanism. By default, a server’s principal name matches the server’s network name (which is specified with the -S parameter or the DSQUERY environment variable). Use the -R parameter when the server’s principal name and network name are not the same.
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
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/Server Configuration Guide.
provides backward compatibility with bcp version 10.0.4 for copying operations involving nullable columns.
Copies data out of the publishers table in character format (using char for all fields) using the -c parameter. The -t field_terminator parameter ends each field with a comma, and the -r row_terminator parameter ends each line with a Return. bcp prompts only for a password:
In UNIX platforms:
bcp pubs2..publishers out pub_out -c -t , -r \\r
The first backslash before the final “r” escapes the second so that only one backslash is printed.
In Windows NT:
bcp pubs2..publishers out pub_out -c -t , -r \r
Copies data from the publishers table to a file named pub_out for later reloading into Adaptive Server. Press Return to accept the defaults specified by the prompts. The same prompts appear when you copy 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 length of field pub_name [40]: Enter field terminator [none]: Enter the file storage type of field city [char]: Enter prefix length of field city [1]: Enter length of field city [20]: Enter field terminator [none]: Enter the file storage type of field state [char]: Enter prefix length of field state [1]: Enter length of field state [2]: Enter field terminator [none]:
In UNIX, you are then asked:
Do you want to save this format information in a file? [Y-n] y Host filename [bcp.fmt]: pub_form Starting copy... 3 rows copied. Clock Time (ms.): total = 1 Avg = 0 (3000.00 rows per sec.)
Copies data back into Adaptive Server using the saved format file, pub_form:
bcp pubs2..publishers in pub_out -f pub_form
Enter the single letter exactly as it appears below:
To see examples of 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
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
(UNIX only) Specifies that you are using a Macintosh, running bcp on a workstation that is using roman8, with the file system on another machine that uses iso_1:
bcp pubs2..publishers in -a mac -J roman8 -q iso_1
Specifies that Adaptive Server send 40K of text or image data using a packet size of 4096 bytes:
bcp pubs2..publishers out -T 40960 -A 4096
Use this syntax for bcp_r if you are using threaded drivers.
Use this syntax for bcp_dce if you are using threaded drivers in the IBM platform.
The current version of bcp ignores the -y sybase_directory parameter.
You cannot use named pipes to copy files in or out.
Error message format is different than earlier versions of bcp. If you have scripts that perform routines based on the values of these messages you may need to rewrite 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.
You must have an Adaptive Server account and the appropriate permissions on the database tables or views, as well as the operating system files to use in the transfer to use bcp.
To copy data into a table, you must have insert permission on the table.
To copy a table to an operating system file, you must have select permission on the following tables:
the table to copy
sysobjects
syscolumns
sysindexes
sysaudits_01 – sysaudits_08
See Chapter 3, “Using bcp to Transfer Data to and from Adaptive Server” for an in-depth discussion of bcp.
See the Performance and Tuning Guide for more information on how changing certain parameters can affect bcp for large batches.
Commands – insert
Sytem procedures – sp_audit, sp_dboption, sp_displayaudit
