Copies a database table to or from an operating system file in a user-specified format.
bcp provides a convenient and high-speed method for transferring data between a database table or view and an operating system file. bcp can read or write files in a wide variety of formats. When copying in from a file, bcp inserts data into an existing database table; when copying out to a file, bcp overwrites any previous contents of the file.
(UNIX) $SYBASE/$SYBASE_OCS/bin.
(Windows) %SYBASE%\%SYBASE_OCS%\bin, as bcp.exe.
UNIX platforms – use bcp_r. The executable is in the same directory as the standard bcp command.
Windows – use the standard bcp.exe utility.
bcp [[database_name.]owner.]table_name [: partition_id | slice_number [partition partition_name] {in | out} [datafile] [-a display_charset] [-A packet_size] [-b batch_size] [-c] [-C] [-d discardfileprefix] [-e errfile] [-E] [-f formatfile] [-F firstrow] [-g id_start_value] [-i input_file] [-I interfaces_file] [-J client_character_set] [-K keytab_file] [-L lastrow] [-m maxerrors] [-MLabelName LabelValue] [-labeled] [-n] [-N] [-o output_file] [-P password] [-Q] [-r row_terminator] [-R remote_server_principal] [-S server] [-t field_terminator] [-T text_or_image_size] [-U username] [-v] [-V [security_options]] [-W] [-x trusted.txt_file] [-X] [-y alternate_home_directory] [-Y ] [-z language] [-Z security_mechanism] [--colpasswd [[[db_name.[owner].]table_name.] column_name [password]]] [--keypasswd [[db_name.[owner].]key_name [password]]] [--hide-vcc] [--initstring "TSQL_command"] [--maxconn maximum_connections] [--show-fi] [--skiprows nSkipRows]
Error in attempting to determine the size of a pair of translation tables. : 'stat' utility failed.
bcp pubs2..titles out table_out -A 4096
To improve the performance of large bulk-copy operations, use network packet sizes that are larger than the default value.
We recommend that you use -e errorfile with -d discardfileprefix to help identify and diagnose the problem rows logged in the discard file.
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 inserted rows exceeds the maximum possible IDENTITY column value, the SAP ASE server returns an error.
By default, when you bulk copy data from a table with an IDENTITY column, bcp excludes all information about the column from the output file. If you specify the -E flag, bcp copies the existing IDENTITY column values into the output file.
The -E parameter has no effect when you are bulk copying data out. the SAP ASE server copies the ID column to the data file, unless you use the -N parameter.
You cannot use the -E and -g flags together.
Do not use -F when performing heavy-duty, multiprocess copying, as doing so 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.
-J client_charset requests that the SAP ASE server convert to and from client_charset, the character set used on the client.
-J with no argument disables character set conversion. 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. For more information about character sets and associated flags, see the Adaptive Server Enterprise System Administration Guide.
If you do not supply the -K option, the bcp user must be logged in to Kerberos with the same user name as specified with the -U option.
For exporting data, this option indicates that you want the sensitivity label of every row to be copied out as the first field.
If you use multiple partitions, the same number of maxerrors is used for every file.
curread (current reading 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 is 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.
LabelValue is the actual value of the label, expressed in the human-readable format used on your system (for example, “Company Confidential Personnel”).
bcp in native format for data recovery or salvage or to resolve an emergency situation.
bcp in native format to transport data between different hardware platforms, different operating systems, or different major releases of SAP ASE.
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 the SAP ASE server and it may be impossible to recover the data. If you cannot re-run 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.
You cannot use both -N and -E parameters when copying data in.
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). 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).
SDK binaries like bcp have the same names in both the 32-bit and 64-bit products. Installing SAP ASE, the SDK, or Open Server 64-bit products with other 32-bit products overwrites the 32-bit binaries. Starting with SAP ASE 15.0.2 and SDK/Open Server 15.0 ESD #9, the 64-bit binaries have been replaced with 32-bit binaries on all 64-bit UNIX platforms. Since 32-bit binaries are included in the 64-bit EBF, the -v option of bcp is no longer a valid way to check the EBF number for 64-bit products. Instead, use the UNIX strings and grep commands to confirm the EBF numbers for SAP ASE.
strings -a libsybct64.a | grep Sybase
Sybase Client-Library/15.5/P/DRV.15.5.0/SPARC/Solaris 8/BUILD1550-001/64bit/OPT/Mon Aug 10 23:04:17 2009
strings -a libsybsrv64.a | grep Sybase
Sybase Server-Library/15.5/P/DRV.15.5.0/SPARC/Solaris 8/BUILD1550-001/64bit/OPT/Mon Aug 10 23:06:27 2009
To enable additional security services, follow -V with a security_options string of key-letter options:
c – enables data confidentiality service.
i – enables data integrity service.
m – enables mutual authentication for connection establishment.
o – enables data origin stamping service.
r – enables data replay detection.
q – enables out-of-sequence detection.
If you use this option, the CS_SEC_NON_ENCRYPTION_RETRY connection property is set to CS_FALSE, and plain text (unencrypted) passwords are used, the connection is not retried.
This option results in normal or extended password encryption, depending on connection property settings at the server. If CS_SEC_ENCRYPTION is set to CS_TRUE, normal password encryption is used. If CS_SEC_EXTENDED_ENCRYPTION is set to CS_TRUE, extended password encryption is used. If both CS_SEC_ENCRYPTION and CS_SEC_EXTENDED_ENCRYPTION are set to CS_TRUE, extended password encryption is used as the first preference.
If bcp fails, 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.
During bcp out, all character-set conversion is done in the server.
Add languages to an SAP ASE server during installation or afterwards, using either the langinstall utility (langinst in Windows) or the sp_addlanguage system procedure.
Unrecognized localization object. Using default value 'us_english'. Starting copy... => warning.
Security mechanism names are defined in the $SYBASE/install/libtcl.cfg configuration file. The default mechanism is used if you do not supply security_mechanism name.
If you use this option, the SAP ASE server does not calculate or send virtual computed column data.
Result sets issued by the initialization string are silently ignored, unless an error occurs. If the SAP ASE server returns an error, bcp stops before data is transferred, and displays an error message.
bcp_r --maxconn 2If you do not include this option, bcp uses the default value of 10.
bcp pubs2..publishers out pub_out -c -t , -r \\r
bcp pubs2..publishers out pub_out -c -t , -r \r
bcp pubs2..publishers out pub_out -C 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]:
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.)
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]:
Do you want to save this format information in a file? [Y-n] Host filename [bcp.fmt]: pub_form Starting copy... 3 rows copied. Clock time (ms.): total = 1 Avg = 0 (3000.00 rows per sec.)
bcp pubs2..publishers in pub_out -f pub_form
bcp pubs2..publishers in datafile -J iso_1 -z french
bcp t1 partition p1 out mypart.dat
bcp t1 partition p1 in mypart.dat
bcp t1 partition p1, p2, p3 in data.first, data.last, data.other
bcp t1 partition p1, p2, p3 out \work2\data\1, \work2\data\b, \work2\data\c
bcp pubs2..titles in titles.txt --initstring 'set replication off'
You need not explicitly reset the configuration option after bcp is finished. If the SAP ASE server returns an error, bcp stops the data transfer and displays an error message.
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 pwd1
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 Enter column db..tbl.col1’s password: ***?
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 < passwordfile
bcp mydb..mytable in myfile –U uuu –p ppp –-keypasswd db..key1 pwd1
bcp pubs2..titles in titlesfile.txt -d reject_
bcp -Vd -SMY_GATEWAY
bcp pubs2..titles in titles.txt -U username -P password --skiprows 2
bcp tempdb..T1 out T1.out -y/work/NewSybase -Uuser1 -Psecret -SMYSERVER
bcp pubs2..publishers out -T 40960 -A 4096
bcp_r --maxconn 2
bcp db_1..t1 out db_1.dat -Usa -P -S big_db -I./interfaces -f ./bcp.fmt
bcp db_1..t1 in db_1.dat -Usa -P -S big_db -I./interfaces -f ./bcp.fmt
bcp pubs2..titles in titles.txt --initstring 'set logbulkcopy on'
You must have an SAP ASE 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.
The table to copy
sysobjects
syscolumns
sysindexes
Values in event and extrainfo columns are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
---|---|---|---|
4 |
bcp |
bcp in |
|
sysaudits_01 – sysaudits_08