存档

文章标签 ‘BCP’,文章数:9

关于BCP工具的使用，介绍三个方面的东西

关于BCP工具的使用，介绍三个方面的东西

1.命令及参数；

2.出现的问题及解决方法；

3.分析bcp出来的文件结构。

----------------------------------------------------------------------------------------------------------

第一：BCP命令参数

bcp （version 11.0.x）

function：

Copies a database table to or from an operating system file in a userspecified format.


Syntax：

bcp  [[database_name.]owner.]table_name {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 sqlini_file]

[-S server] [-a display_charset]
[-q datafile_charset] [-z language] [-v]

[-A packet_size] [-J client_charset]

[-T text_or_image_size] [-E] [-N] [-X]

[-y sybase_dir]

Parameters：

database_name – is optional if the table being copied is in your default database. Otherwise, specify a database name.

owner – is optional if you or the Database Owner own the table being copied. If you do not specify an owner, bcp first looks 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’s name or the command fails.

table_name – is the name of the database table to copy. The table name cannot be a Transact-SQL reserved word.

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.

datafile – is the full path name of an operating system file. The path name can be from 1–255 characters in length.

-m max_errors – 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 eachrejected row as one error. If you do not include this option, bcp uses a default value of 10.

 

 

 

-f format_file – is the full path name of a file with stored responses from a previous use of bcp on the same table. After you answer the bcp format questions, bcp asks if you want 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 do not have to duplicate your previous format responses interactively. Use this option only when you previously created a format file that you want to use now for a copy in or out. If this option is not used, bcp queries you for format information interactively.
 

 

-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 the bcp program appear on your terminal. bcp creates an error file only when you specify this option. If you specify this option, and bcp does not encounter any nonfatal errors, it does not create the error file.

-F firstrow – is the number of the first row to copy (default is the first row).

-L lastrow – is the number of the last row to copy (default is the last row).

 

-b batchsize – is the number of rows per serial batch of data copied (the default is to copy all the rows in one batch). Each batch is a transaction that is committed at the end of the batch. Batching applies only when bulk copying in; it has no effect on bulk copying out.

-n – performs the copy operation using native (operating system) formats. This option does not prompt for each field. Files in native data format are not human-readable.

-c – performs the copy operation with char datatype as the default.Use this format if you are sharing data between platforms. 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.

-t field_terminator – specifies the default field terminator.

-r row_terminator – specifies the default row terminator.

Note：

When specifying terminators from the command line with the -t or -r option, escape characters that have special significance to the Windows NT Command Prompt shell (see example 1 on page 1-11). 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 a SQL Server login name. If you do not specify username, bcp uses value of the USERNAME environment variable (the current user’s operating system login name, not the Sybase user name and login).

-P password – specifies a SQL Server password. If you do not specify -P password, bcp prompts for a password. If your password is NULL, place the -P flag at the end of the command line by itself.

-I sqlini_file – specifies the name and location of the interfaces file (sql.ini) to search when connecting to SQL Server. If you do not specify -I, bcp looks for a file named sql.ini in the ini subdirectory of your Sybase release directory.

-S server – specifies the name of the SQL Server to connect to. If you specify -S with no argument, bcp uses the server specified by the DSQUERY environment variable.

-a display_charset – runs bcp from a terminal where the character set differs from that of the machine on which bcp is running. (See the System Administration Guide for more information about changing character sets.) -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.

-q datafile_charset – runs bcp to copy character data to or from a file system that uses a character set different from the client character set. -q in conjunction with -J specifies 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 was designed totranslate only from terminal display to SQL Server, not from SQL Server to the terminal.

Note：

The ascii_7 character set is compatible with all character sets. If either the SQL Server’s or 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.

 

-z language – is 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 a SQL Server during installation or add them afterward with the langinstall utility or the stored procedure sp_addlanguage.
 

 

-v – displays the version number of bcp and a copyright message and returns to the operating system.

-A packet_size – specifies the network packet size to use for this bcp session. For example: bcp -A 2048 sets the packet size to 2048 bytes for this bcp session. size must be between the values of the default network packet size and max network packet size configuration parameters, one-third the size of the additional network memory configuration parameter, and a multiple of 512. To improve the performance of large bulk copy operations, use network packet sizes that are larger than the default.

-J client_charset – specifies the character set to use on the client. bcp uses a filter to convert input between client_charset and the SQL Server character set. -J client_charset requests that SQL 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 parameter if the client and server use the same character set.The default may not necessarily be the character set that the client is using. See the System Administration Guide for more information about character sets and the associated flags.

-T text_or_image_size – specifies in bytes the maximum length of text or image data that SQL 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.

-E – explicitly specifies the value of a table’s IDENTITY column. By default, when you bulk copy data into a table with an IDENTITY column, the host file must contain a placeholder for the IDENTITY column (a value of 0 is recommended). The server assigns the row a unique, sequential IDENTITY column value, as bcp inserts each row into the table. If the number of inserted rows exceeds the maximum possible IDENTITY column value, SQL Server returns an error message. To use an explicit IDENTITY column value from the host file for each row, specify the -E flag when copying data into a table. The -E option has no effect on bulk copying out.

 

-N – skips the IDENTITY column. Use this option when you copy data in, if your host data file does not include a placeholder for the IDENTITY column values, or when you copy data out and you do not want to include the IDENTITY column information in the host file

 

-X – when connecting to the server, bcp initiates the login with clientside 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 theserver 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.

-y sybase_dir – specifies a Sybase directory other than the default Sybase release directory.

 

第二：出现的问题及解决方法

阅读全文...

标签: