bcp
Description
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
Description
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.
Syntax
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
Parameters
database_name
is optional if the table being copied is in your default database or in master. Otherwise, you must specify a database name.
owner
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.
view_name
is the name of the view you are copying out.
table_name
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.
slice_number
is the number of the slice of the database table to copy.
partition_id
is the identifier of the partition into which to copy.
in | out
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.
datafile
is the full path name of an operating system file. The path name can be from 1 to 255 characters in length.
-m maxerrors
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.
-f formatfile
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.
-e errfile
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.
-F firstrow
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.
-L lastrow
is the number of the last row to copy from an input file (default is the last row).
-b batchsize
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.
-n
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.
-c
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.
-t field_terminator
specifies the default field terminator.
-r row_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).
-U username
specifies an Adaptive Server login name.
-P password
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.
-I interfaces_file
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).
-S server
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.
-a display_charset
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) req