SYBASE: BCP command reference

 

Sybase Database BCP description

BCP is a tool provided by Sybase for database table-level data backup.

It is generally stored in the bin directory of the installed ASE or open client.

For ASE earlier than version 12, the BCP storage directory is $ Sybase/bin.

The directory to be stored after version 12 (including version 12) is $ Sybase/OCS-12_x/bin.

$ Sybase is the Sybase installation directory, and 12_x represents versions 12.0 and 12.5, which are displayed as 12_0 or 12_5.

The executable file name is BCP. EXE.

The parameter list is as follows:

(Available BCP)

Usage: 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] [-v]

[-A packet size] [-J client character set]

[-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_mechanic] [-q]

Common Data Backup formats:

BCP dbname .. tablename out c: \ temp \ filename-USA-ppassword-sservername-C

You can.

-U indicates the Sybase logon name,-P indicates the Sybase logon password, and-s indicates the Sybase service name.-C indicates that the data is exported in visible text mode.

To recover data, replace out with in.

You can use the following method to generate an execution script that can export data from all tables in a database at a time.

Edit a file named bcpscript as follows:

Use dbname select the database to export data

Go

Select 'bcp dbname .. '+ name +' out C: \ temp \ '+ name +'-USA-p-ssybcdsrv-C' from sysobjects where type = 'U' in the sysobjects system table, the table with type U is the user table, the system table is S.

Go

Run the command in the following format:

ISQL-USA-ppassword-sservername-I bcpscript-O bcpout. bat

After the I parameter, It is the input file, and after the O parameter, the file is the output file obtained after the input file is executed.

After execution, you can get a batch file with the suffix BAT (a shell file is generated in UNIX and the corresponding execution permission is changed), which can be executed directly. That is, the corresponding data file is exported under the specified directory. The data of a table is a file. For example, in UNIX, the bat suffix is not required.

If you need to restore the data files backed up to the database again, you only need to replace the out parameter in the BCP command in the preceding operation steps with the in parameter.

Note: You are advised to perform some tests before performing operations on the official data.

In addition, you can use the-J charset parameter to modify the character set using BCP.

For more information about how to use tools such as BCP, see Sybase.

Perform the following steps for system transplantation:

1. Install the hardware environment of the new environment, including the network and hard disk status;

2. Install the operating system of the new environment, including the service pack;

3. Install sybase database products of the same version, including patches;

4. Add database users, devices, and other related information, which should be consistent with those in the old system.

5. Create a New System database;

6. use your table script or the DDL generation function in Sybase Central to export the table creation script in the old system to generate tables in the database. it is best to separate the table creation script from the script that creates table constraints (primary keys, foreign keys, and so on). First, do not create constraints on the table. After the data is imported, Add .;

7. BCP out data in the old system, according to the method mentioned above;

8. run scripts for creating other objects, including indexes, primary keys, foreign keys, stored procedures, triggers, and defaults;

9. modify the structure of the table to be modified in the new system or add a new table;

10. test whether the application system works properly.

 

 

 

 

BCP indicates copying a database table to or from an operating system file in the format specified by the user. BCP is located in $ Sybase/$ sybase_ocs/bin.
The Windows NT utility is bcp.exe, which is located in % Sybase % \ % sybase_ocs % \ 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]
[-Z language]
[-A packet_size]
[-J client_charset]
[-T text_or_image_size]
[-E]
[-G id_start_value]
[-N]
[-X]
[-K keytab_file]
[-R remote_server_principal]
[-V [security_options]
[-Z security_mechanic]
[-Q]
[-Y]
Or
BCP-V
Parameter database_name
This parameter is optional if the table being copied is in the default database or master. Otherwise, the database name must be specified.

Owner
This parameter is optional if you or the database owner owns the table being copied. If no owner is specified, BCP first checks whether your table has this name, and then finds
Whether the table owned by the database owner has this name. If other users own this table, the owner name must be specified; otherwise, the command will fail.
View_name
The view name being copied.
Table_name
Is the name of the database table to be copied. The table name cannot be a reserved wordcount of transact-SQL. The Partition Number partition_number does not exist in Table table_name.
Slice_number
The ID of the Data Segment of the database table to be copied.
Partition_id
Is the identifier of the partition to be copied.
In | out
Is the replication direction. In indicates copying from a file to a database table; out indicates copying from a database table or view to a file.
Datafile
Is the full path name of the operating system file. The path name can be 1 to 255 characters in length.
-M maxerrors
The maximum number of non-fatal errors allowed before BCP terminates replication. BCP abandons each row that cannot be inserted (due to a conversion error, or attempts to insert null values to columns that do not allow null values), and counts each abandoned row as an error. If this parameter is not included,
BCP uses the default value 10.
-F formatfile
Is the complete path name of the file, which stores the response from the last time BCP was used on the same table. After answering the BCP format question, it prompts you to save the answer to a format file. Format File Creation is optional. The default file name is BCP. FMT. The BCP program can reference format files when copying data, so that users do not have to repeat previous format responses in interactive mode. The-F parameter is used only when you have previously created a format file that you want to use for record-in or copy-out. If this parameter is not specified, BCP will interactively ask the user about the format information.
-E errfile
Is the full path name of the error file. BCP stores all rows that cannot be transferred from the file to the database. Error messages from BCP are displayed on the terminal. BCP creates an error file only after this parameter is specified.
-F firstrow
Is the row number of the first row to be copied from the input file (the first row by default ). Avoid using the-F option when executing the heavy task of multi-process replication, because it usually causes bcp to occupy more resources for running and cannot speed up the process. Instead, use-F for a single process for ad hoc replication.
-L lastrow
Is the row number of the last row to be copied from the input file (the default is the last row ).
-B batchsize
Is the number of rows replicated in each batch of data (by default, all rows of a batch of data are copied ). Batch copy is only applicable to batch copy. The minimum batchsize accepted by BCP is 1.
-N
Perform the copy operation in the local (operating system) format. Specifying the-n parameter means that BCP will not prompt each field. Files in the local data format are manually unreadable.
-C
The Char data type is used as the default data type of all columns in the data file for replication. This format is used if you want to share data between platforms. This parameter does not prompt each field. It uses char as the default storage type, has no prefix, uses \ t (Tab) as the default field Terminator, and uses \ n (line break) as the default line terminator.
-T field_terminator
Specifies the default field Terminator.
-R row_terminator
Specifies the line terminator.
-U Username
Specify the login name of the Adaptive Server.
-P Password
Specify the adaptive server password. If-ppassword is not specified, BCP prompts you to enter a password. If the password is null, the-P Flag can be omitted.
-I interfaces_file
Specify the name and location of the interface file to be searched when you connect to the Adaptive Server. If-I is not specified, BCP searches for the interface file (SQL. ini in Windows NT) in the directory specified by Sybase environment variable (INI directory in Windows NT ).
-S server
The name of the Adaptive Server to be connected. If the-S parameter is not specified, BCP uses the server specified by the dsquery environment variable.
-A display_charset
The user is allowed to run BCP on the terminal, and the character set on this terminal is different from that on the computer that is running BCP. Associate-A and-J to specify the character set conversion file (. xlt file) required for conversion ). -A is used independently, instead of-J, only when the client character set is the same as the default character set.

If you use the-a parameter to name a character conversion file, the following error message is displayed:
Error in attempting to determine the size of a pair of translation tables.: 'STAT' utility failed.
-Z language
Is the official name of the alternative language used by the server to display BCP prompts and messages. If the-Z flag is not displayed, BCP uses the default language of the server. You can use the langinstall utility (or langinst in Windows NT) or sp_addlanguage stored procedure to add a language to the Adaptive Server during or after installation.
If the-Z parameter is used to specify an incorrect or unrecognized language, the following error message is displayed:
Unrecognized localization object. Using default value 'us _ inc'. Starting copy... => warning.
-V
Displays the BCP version number and copyright message, and returns to the operating system.
-A packet_size
Specify the size of the network package used for this BCP session. For example, BCP pubs2.. Titles out table_out-A 2048 for this BCP session, set the package size to 2048 bytes. Packet_size must be between the value of the default network packet size and maximum network packet size configuration variable, and must be a multiple of 512. Using a network package larger than the default value can improve the performance of bulk copy operations.
-J client_charset
Specifies the character set used on the client. BCP uses the filter to convert input between the client_charset and Adaptive Server character sets.
-J client_charset requests convert the Adaptive Server character set to the client_charset used on the client.
-J without parameters sets the character set conversion to null. In this way, no conversion will occur. This setting is used if the client and server use the same character set.
If-J is omitted, the character set is set to the default Character Set of the platform, and does not need to be the character set that the client is using.
If the-J parameter is used to specify an incorrect or unrecognized character set, the following error message is displayed:
Unrecognized localization object. Using default value 'iso _ 1'. Starting copy... => warning.
-T text_or_image_size
The maximum length of text or image data sent by the Adaptive Server can be specified in bytes. The default value is 32 KB. If the text or image field is greater than the-T value or default value, BCP will not send this overflow value.
-E
Explicitly specify the value of the identity column of the table. By default, When you copy data to a table that contains the identity column, BCP assigns a temporary identity column value of 0 to each row. This is only valid when copying data to a table. BCP reads the value of the ID column from the data file, but does not send the value to the server. On the contrary, when BCP inserts each row into the table, the server specifies a unique and continuous identity column value for each row. The starting value is 1. If the-e flag is specified when you copy data to a table, BCP reads the column value from the data file and sends the value to the server that inserts the value to the table. If the number of inserted rows exceeds
Maximum possible values in the identity column. The Adaptive Server Returns an error. When data is replicated in batches, the-e parameter does not work. The Adaptive Server copies the ID column to the data file unless you use the-n parameter.
The-E and-G labels cannot be used at the same time.
-G id_start_value
Specify the value of the identity column as the starting point for the ingress entry.
The-G and-e flag cannot be used at the same time.
-N
Skip the identity column. This parameter is used in the following cases: when a volume is imported into the data, the host data file does not contain a placeholder for the value of the identity column; or, when copying data, you do not want the host file to contain the identity column information. The-N and-e parameters cannot be used at the same time.
-X
In this connection with the server, the application starts a login with client password encryption. The BCP (client) notifies the server that it wants to encrypt the password. The server returns a key, BCP uses this key to encrypt the password, and the server uses this key for authentication when the password arrives. If the BCP crashes, the system creates a core file containing the user password. If the encryption option is not used, the password is displayed in plain text format in the file. If the encryption option is used, the password cannot be read.
-K keytab_file
Specifies the path of the keytab file used for DCE authentication.
-R remote_server_principal
Specify the supervisor name for the server according to the security mechanism definition. By default, the server's supervisor name matches the server's network name (specified by the-S parameter or dsquery environment variable. When the server's supervisor name and network name are inconsistent, use the-R parameter.
-V security_options
Network-based user identification. When using this option, you must log on to the network security system before running the utility. In this case, you must use the-u option to provide the network user name. Any password provided with the-P option will be ignored.
-V can be followed by a set of security_options key-letter options to enable other security services.
These key letters include:
· C-enable the data confidentiality Service
· I-enable the data integrity service
· M-enable mutual authentication during connection Establishment
· O-enable the data source and Stamp Service
· R-enable data replay Detection
· Q-enable sequential disorder Detection
-Z security_mechanic
Name of the security mechanism used for connection.
Define the security mechanism name in the $ Sybase/install/libtcl. cfg configuration file. If the security_mechanic name is not provided, the default mechanism is used.
-Q
Provides backward compatibility with BCP version 10.0.4 for copying null columns.
-Y
The specified character set conversion is disabled on the server, but is executed on the client by BCP in.

 

 

 

 

 

 

 

 

 

BCP in
BCP "db_ams... tblpatchpoltrxnmthly1101" in "D: \ patchdata_1101.txt"-B 3000-c-t! -S SERVER_NAME-u username-P Password

BCP out
BCP "db_ams... tblbusnsc_alt_pol_txn_mthly03" out "D: \ patchdataalt_1103.txt"-c-t! -S "SERVER_NAME"-U "username"-P "password"