Copies a database table to or from an operating system file in a user-specified format. This utility is in $SYBASE/$SYBASE_OCS/bin.
bcp [[database_name.]owner.]table_name [: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]
Optional if the table being copied is in your default database or in master. Otherwise, you must specify a database name.
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 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.
The name of the database table to copy. The table name cannot be a Transact-SQL reserved word.
The number of the slice of the database table to copy.
The name of the partition in Adaptive Server. For multiple partitions, use a comma-separated list of partition names.
The direction of the copy. in indicates a copy from a file into the database table, while out indicates a copy to a file from the database table.
bcp raises
an error and stops its operation if the number of rows to be copied in or out exceeds
2147483647.
The full path name of an operating system file. The path name can be from 1 – 255 characters in length. For multiple files, use a comma-separated list of file names. If you enter more than one data file and partition name, the number of files and partitions must be the same.
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 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.
You see this error message if any character translation files are missing, or if you enter file names incorrectly:
Error in attempting to determine the size of a pair of translation tables. : ‘stat’ utility failed.
Specifies the network packet size to use for this bcp session. For example, the following example sets the packet size to 4096 bytes for this bcp session:
bcp pubs2..titles out table_out -A 4096
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 larger-than-default network packet sizes to improve the performance of large bulk-copy operations.
The number of rows per batch of data copied. By default, bcp in copies n rows in one batch, where n is equal to the batch size. Batch size applies only when bulk copying in; it has no effect on bulk copying out. The smallest number bcp accepts for batchsize is 1.
Setting batchsize to 1 causes Adaptive Server to allocate
one data page to one row copied in. This parameter applies only
to fast bcp, and
is useful only for locating corrupt rows of data. Use -b
1 carefully, as doing so causes a new page to be allocated
for each row, which is generally a poor use of space.
Performs the copy operation using the char datatype as the default. This option 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 (newline) as the default row terminator.
Supports bulk copy of encrypted columns if Adaptive Server supports encrypted columns. -C enables the ciphertext option before initiating the bulk copy operation.
Logs the rejected rows into a dedicated discard file. The discard file has the same format as the host file and is created by appending the input file name to the discard file prefix supplied. You can correct the rows in this file and use the file to reload the corrected rows.
Sybase recommends that you use -d discardfileprefix with -e errorfile to help identify and diagnose problem rows in the discard file.
The full path name of an error file where bcp stores all rows that bcp was unable to transfer from the file to the database. The error messages from bcp appear on your terminal, and are also logged in the error file. bcp creates an error file only when you specify this parameter. If multiple sessions are used, the partition and file name information for the error is added to the error file.
Sybase recommends that you use -e errorfile with -d discardfileprefix to help identify and diagnose problem rows in the discard file.
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 inserted rows exceeds the maximum possible IDENTITY column value, Adaptive 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. 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.
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 need not interactively duplicate your previous format responses. Use this parameter only if you previously created a format file that you want to use now for a copy in or out. If you do not use this option, you must interactively enter format information.
The number of the first row to copy (the default is the first row). If you use multiple files, this option applies to each file.
Do not use this parameter when performing heavy-duty, multiprocess 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.
You cannot use -F with --skiprows.
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.
Specifies the name of the input file. Standard input (stdin) is the default.
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 the interfaces file, interfaces, in the Sybase release directory.
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_character_set requests that Adaptive Server convert to and from client_character_set, 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.
(Used only with DCE security). Specifies a DCE keytab file that contains the security key for the user name specified with -U option. Create keytab with the DCE dcecp utility. See your DCE documentation.
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.
The number of the last row to copy from an input file (the default is the last row). If you use multiple files, this option applies to each file.
The maximum number of 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), each rejected row as one error. If you do not include this option, bcp uses a default value of 10.
If you use multiple partitions, the same number of maxerrors is used for every file.
(secure SQL Server only) enables multilevel users to set the session labels for the bulk-copy. Valid values for LabelName 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 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 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 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”).
(secure SQL Server only) indicates that the data you are importing already has labels in the first field of every record.
For exporting data -labeled indicates that you want the sensitivity label of every row to be copied out as the first field.
Performs the copy operation using native (operating system) formats. Specifying the -n parameter means bcp does 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, 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 get 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 re-run bcp in character format (for example, a table was truncated or dropped, hardware damage occurred, a database table was dropped, and so on), the data is unrecoverable.
Skips the IDENTITY column. Use this option when copying data in if your host data file does not include a placeholder 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 the name of the output file. Standard output (stdout) is the default.
Specifies an Adaptive Server password. If you do not specify -P password, bcp prompts for a password. You can leave out the -P flag if your password is NULL.
Provides backward compatibility with bcp for copying operations involving nullable columns.
Specifies the 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). Use the -R option 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 specified by your DSQUERY environment variable.
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.
Displays the current version of bcp and a copyright message and returns to the operating system.
SDK binaries like bcp have the same names in both the 32-bit and 64-bit products. Installing Adaptive Server, the SDK, or Open Server 64-bit products with other Sybase 32-bit products overwrites the 32-bit binaries. Starting with Adaptive Server 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 both Open Client and Open Server.
For example, to find the string containing the EBF number in the libsybct64.a library, enter:
strings -a libsybct64.a | grep Sybase
This returns a string similar to:
Sybase Client-Library/15.5/P/DRV.15.5.0/SPARC/Solaris 8/BUILD1550-001/64bit/OPT/Mon Aug 10 23:04:17 2009
To find the string containing the EBF number in the libsybsrv64.a library, enter the following:
strings -a libsybsrv64.a | grep Sybase
This returns a string like the following:
Sybase Server-Library/15.5/P/DRV.15.5.0/SPARC/Solaris 8/BUILD1550-001/64bit/OPT/Mon Aug 10 23:06:27 2009
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 that enables additional security services:
c – enable data confidentiality service.
d – enable credential delegation and forward the client credentials to the gateway application.
i – enable data integrity service.
m – enable mutual authentication for connection establishment.
o – enable data origin stamping service.
q – enable out-of-sequence detection.
r – enable data replay detection.
Specifies that if the server to which bcp is attempting to connect supports neither normal password encryption nor extended password encryption, plain text password retries are disabled. If this option is used, the CS_SEC_NON_ENCRYPTION_RETRY connection property is set to CS_FALSE, and plain text (unencrypted) passwords are not used in retrying the connection.
Specifies an alternate trusted.txt file
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.
This option can result 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.
Sets an alternate Sybase home directory.
Specifies that the character set conversion is disabled in the server, and is performed by bcp on the client side when using bcp out.
All character set conversion is done in the server during bcp out.
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.
You can add languages to an Adaptive Server during installation or afterwards, using either the langinst utility 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.
Specifies the name of a security mechanism to use on the connection.
Security mechanism names are defined in the libtcl.cfg configuration file, which is located in $SYBASE/$SYBASE_OCS/config. If no security_mechanism name is supplied, the default mechanism is used.
The CS_LIBTCL_CFG property
specifies the name and path to an alternative libtcl.cfg file.
For details about this property, see the Open Client and
Open Server Client Libraries Reference Manual.
For more information about security mechanism names, see the description of the libtcl.cfg file in the Open Client and Open Server Configuration Guide for UNIX.
Sets passwords for encrypted columns by sending “set encryption passwd password for column column_name” to Adaptive Server. This does not automatically apply passwords to other encrypted columns, even if the second column is encrypted with the same key. Supply the password a second time to access the second column.
Instructs bcp not to copy virtual computed columns (VCC) either to or from a data file. When you use this parameter in bcp OUT, the datafile does not contain data for VCC; in bcp IN, the data file may not contain data for a VCC.
If you use this option, Adaptive Server does not calculate or send virtual computed column data.
Sends Transact-SQL commands to Adaptive Server before data is transferred.
Result sets issued by the initialization string are silently ignored, unless an error occurs. If Adaptive Server returns an error, bcp stops before data is transferred, and displays an error message.
Sets passwords for all columns accessed by a key by sending “set encryption passwd password for key key_name” to Adaptive Server.
The maximum number of parallel connections permitted for each bulk copy operation. You must use bcp_r, the threaded version of the bcp utility, to copy multiple files in parallel. For example, the following example sets the maximum number of parallel connection permitted for each operation to 2:
bcp_r --maxconn 2
If you do not include this parameter, bcp uses a default value of 10.
Instructs bcp to copy functional indexes, while using either bcp IN or bcp OUT. If you do not specify this parameter, Adaptive Server generates the value for the functional index.
Instructs bcp to skip a specified number of rows before starting to copy from an input file. The valid range for --skiprows is between 0 and the actual number of rows in the input file. If you provide an invalid value, you see an error message.
You cannot use --skiprows with the -F option.
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 only one backslash prints:
bcp pubs2..publishers out pub_out -c -t , -r \\r
The -C parameter copies data out of the publishers table (with encrypted columns) in cipher-text format instead of plain text. Press Return to accept the defaults specified by the prompts. The same prompts appear when copying data into the publishers table.
bcp pubs2..publishers out pub_out -C
Password:
Enter the file storage type of field col1 [int]:
Enter prefix length of field col1 [0]:
Enter field terminator [none]:
Enter the file storage type of field col2 [char]:
Enter prefix length of field col2 [0]:
Enter length of field col2 [10]:
Enter field terminator [none]:
Enter the file storage type of field col3 [char]:
Enter prefix length of field col3 [1]:
Enter field terminator [none]:
Copies data from the publishers table to a file named pub_out for later reloading into Adaptive Server. Press Return to accept 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 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]:
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 out of partition p1 of table t1 to the mypart.dat file in the current directory:
bcp t1 partition p1 out mypart.dat
Copies data back into Adaptive Server using the saved format file, pub_form:
bcp pubs2..publishers in pub_out -f pub_form
Copies a data file created with a character set used on a VT200 terminal into the pubs2..publishers table. The -z flag displays bcp messages in French:
bcp pubs2..publishers in vt200_data -J iso_1 -z french
Copies files data.first, data.last and data.other into partitions p1, p2, and p3, respectively:
bcp t1 partition p1, p2, p3 in data.first, data.last, data.other
Copies the mypart.dat file from the current directory, into table t1 of partition p1:
bcp t1 partition p1 in mypart.dat
Copies partitions p1, p2 and p3 to files a, b, and c, respectively, in the \work2\data directory:
bcp t1 partition p1, p2, p3 out \work2\data\a, \work2\data\b, \work2\data\c
Copies files data.first, data.last and data.other into partitions p1, p2, and p3, respectively:
bcp t1 partition p1, p2, p3 in data.first, data.last, data.other
Disables replication when titles.txt data is transferred into the pubs2 titles table:
bcp pubs2..titles in titles.txt -- initstring “set replication off”
Because the set replication off command
in this example is limited to the current session in Adaptive Server,
there is no need to explicitly reset the configuration option when bcp is finished.
Sets the password to pwd1 for the encrypted column col1:
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 pwd1
Sets a prompt to enter the password for encrypted column col1:
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 Enter column db..tbl.col1’s password: ***?
Reads the password for encrypted column col1 from an external OS file named “passwordfile”:
bcp mydb..mytable out myfile –U uuu –P ppp –-colpasswd db..tbl.col1 < passwordfile
Sets password pwd1 for encryption key key1:
bcp mydb..mytable in myfile –U uuu –p ppp –-keypasswd db..key1 pwd1
Creates the discard file reject_titlesfile.txt:
bcp pubs2..titles in titlesfile.txt -d reject_
For MIT Kerberos, requests credential delegation and forwards the client credentials to MY_GATEWAY:
bcp -Vd -SMY_GATEWAY
bcp ignores the first two rows of the input file titles.txt, and starts to copy from the third row:
bcp pubs2..titles in titles.txt -U username -P password --skiprows 2
Sets an alternate Sybase home directory:
bcp tempdb..T1 out T1.out -y/work/NewSybase -Uuser1 -Psecret -SMYSERVER
bcp_r is a threaded version of bcp. You must use bcp_r if a security service, such as Kerberos, or a directory service, such as LDAP, is used.
You cannot use named pipes to copy files in or out.
Using --hide-vcc improves performance, as Adaptive Server does not transfer and calculate data from virtual computed columns.
Although you can use any Transact-SQL command with --initstring as an initialization string for bcp, you must reset possible permanent changes to the server configuration after running bcp. You can, for example, reset changes in a separate isql session.
slice_number is included for backward compatibility with Adaptive Server 12.5.x and earlier, and can be used only with round-robin-partitioned tables.
You can specify either slice_number or partition partition_name, not both.
If you do not specify partition_name, bcp copies to the entire table.
You can specify multiple partitions and data files. Separate each partition name or data file with commas.
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.
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.
bcp 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 and skips the row. bcp does not insert truncated data into the table. The conversion error is as follows:
cs_convert: cslib user api layer: common library error: The result is truncated because the conversion/operation resulted in overflow
To keep track of data that violates length requirements, run bcp with the -e log-file name option. bcp records the row and the column number of the rejected data, the error message, and the data in the log file you specify.
To restrict the functionality of bcp to that of a previous version, 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.
If bcp is invoked and no value is supplied for the -c, -f, or -n parameters, a bcp prompt requests the file storage type. The file storage type can be any valid Adaptive Server datatype. Storage types for the bigdatetime and bigtime Adaptive Server datatypes are specified as:
Storage type |
Table datatype |
|---|---|
A |
bigdatetime |
B |
bigtime |
You can specify these datatypes for a bcp format file using the bigdatetime or bigtime datatypes.
Storage format |
Adaptive Server datatype |
|---|---|
SYBBIGDATETIME |
bigdatetime |
SYBBIGTIME |
bigtime |
Specifying the -d option applies only when bulk copying in; it is silently ignored when used in bulk copying out.
If you use multiple input files, one discard file is created for every input file that has an erroneous row. If there are no rejected rows, no discard file is created.
If bcp reaches the maximum errors allowed and stops the operation, all the rows, from the beginning of the batch until the failed row are logged.
If you use the -d option, the batch size is automatically adjusted. You see a warning message if you:
Specify -b batchsize, but the batch or row size is too big to hold all the rows of the batch in memory or
Do not specify -b batchsize.
bcp 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 row insertions are 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 that are implicitly created using the unique integrity constraint of a create table command. 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 sp_dboption DBNAME,”select into/bulkcopy”,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 need not set this option to copy data out to a file or 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 to truncate
the log after the bulk copy completes, and 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! Ensure that you dump your database before you turn off the select into/bulkcopy flag. If you have inserted unlogged data into your database, and you then perform a dump transaction before performing a dump database, you cannot recover your data.
Unlogged bcp runs 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 can be dumped.
select into/ bulkcopy on |
select into/ bulkcopy off |
|
|---|---|---|
Fast bcp (no indexes or triggers on target table) |
Yes dump transaction prohibited |
No Adaptive Server forces slow bcp |
Slow bcp (one or more indexes or triggers) |
Yes dump transaction prohibited |
Yes dump transaction OK |
By default, the select into/bulkcopy option is off in newly created databases. To change the default, turn the option on in the model database.
The performance penalty for copying data into a table
that has indexes or triggers can be severe. If you are copying in
a large number of rows, it may be faster to first use drop index
(or alter table for indexes...) and drop trigger to drop all the
indexes and triggers; set the database option; copy the data into
the table; re-create the indexes and triggers; and then dump the
database. However, you must allocate extra disk space for the construction
of indexes and triggers—about 2.2 times the amount of space
needed for the data.
When you copy data in or out using the -n (native format) or -c (character format) option, bcp prompts only 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 data that follows
The storage length of the data in the file for non-NULL fields
The field terminator, which can be any character string
Scale and precision for numeric and decimal datatypes
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 terminators
Accept the default lengths
Table A-3 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 Adaptive Server datatype where implicit conversion is supported |
Prefix length |
|
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:
|
bcp can copy data out to a file either as its native (database) datatype, or as any datatype for which implicit conversion is supported. bcp copies user-defined datatypes as their base datatype or as any datatype for which implicit conversion is supported. See dbconvert in the Open Client DB-Library/C Reference Manual.
Be careful when you copy data from different versions
of Adaptive Server, because not all versions support the same datatypes.
A prefix length is a 1-byte, 2-byte, or 4-byte integer that represents the length of each data value in bytes. It immediately precedes the data value in the host file.
Be sure that 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 silently 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 newline (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 cannot identify which tab represents the end of the string. bcp always looks for the first possible terminator, in this case, it will 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 smaller 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.
Table A-4 and Table A-5 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. “...” indicates that the pattern repeats for each field. The field length is 8 for each column, and “string” represents the 6-character field each time.
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 |
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. However, 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 data row.
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 in character format from a file into a database table, check the datatype entry rules in the “Datatypes” chapter of the Adaptive Server Enterprise Reference Manual. Character data that is copied into the database with bcp must conform to those rules. 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 specified with the -q parameter is missing, or you mistyped the name.
