Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the parameters of the BCP command are, which can be used for reference. Interested friends can refer to them. I hope you will gain a lot after reading this article.

After exporting the data table, the command is BCP PROFILE.dbo.tblODODIFFERENCE out D:\ temp\ 20100826\ tblODODIFFERENCE_20100826.csv-S GBGD00-GB02\ GBUK-T-Q-w-t

Several parameters:

-S server_ name [\ instance_name]

Specifies the SQL Server instance to connect to. Specify server_name to connect to the default instance of SQL Server on the server. Specify server_name\ instance_name to connect to the named instance of SQL Server 2000 on the server. If no server is specified, bcp connects to the default instance of SQL Server on the local computer. This option is required when performing bcp from a remote computer on the network.

-T

Specifies that bcp uses the network user's security credentials to connect to the SQL Server through a trust connection. Login_id and password are not required.

-Q

Execute the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and the SQL Server instance. Use this option to specify the name of the database, owner, table, or view that contains spaces or quotation marks. Enclose the entire three-part table or view name in double quotes (").

-w

Use Unicode characters to perform a bulk copy operation. This option does not prompt for each field; it uses nchar as the storage type without a prefix,\ t (tab) as the field delimiter, and\ n (newline) as the line Terminator. Cannot be used in SQL Server version 6.5 or earlier.

-t field_term

Specifies the field Terminator. The default field Terminator is\ t (tab). Use this parameter instead of the default field Terminator.

To separate each column.

Let's introduce the parameters of BCP based on this command:

Bcp syntax

Bcp {[[database_name.] [owner].] {table_name | view_name} | "query"}

{in | out | queryout | format} data_file

[- m max_errors] [- f format_file] [- e err_file]

[- F first_row] [- L last_row] [- b batch_size]

[- n] [- c] [- w] [- N] [- V (60 | 65 | 70)] [- 6]

[- Q] [- C code_page] [- t field_term] [- r row_term]

[- I input_file] [- o output_file] [- a packet_size]

[- S server_ name [\ instance_name]] [- U login_id] [- P password]

[- T] [- v] [- R] [- k] [- E] [- h "hint [,... n]"]

Parameter description:

Parameters.

Database_name

The name of the database in which the specified table or view resides. If not specified, the user defaults to the database.

Owner

The name of the table or view owner. Owner is optional if the user performing the bulk copy operation has the specified table or view. If no owner is specified and the user performing the bulk copy operation does not own the specified table or view, then Microsoft? SQL Server 2000 returns an error message and cancels the bulk copy operation.

Table_name

Is the name of the destination table when copying data to SQL Server (in) and the source table name when copying data from SQL Server (out).

View_name

Is the name of the destination view when copying data to SQL Server (in) and the source view name when copying data from SQL Server (out). Only views in which all columns refer to the same table can be used as destination views. For more information about the limitations of copying data to a view, see INSERT.

Query

Is an Transact-SQL query that returns a result set. If the query returns multiple result sets, such as the SELECT statement that specifies the COMPUTE clause, only the first result set will be copied to the data file, and the subsequent result set will be ignored. Use double quotation marks to cause the query statement, and use single quotation marks to cause anything embedded in the query statement. Queryout must also be specified when bulk copying data from a query.

In | out | queryout | format

Specifies the direction of bulk copy. In is copied from a file to a database table or view, and out is copied from a database table or view to a file. Queryout must be specified only when bulk copying data from a query. Format creates a format file based on the specified options (- n,-c,-w,-6, or-N) and the table or view delimiter. If you use format, you must also specify the-f option. Indicates that the bcp utility in Microsoft SQL Server 6.5 does not support bulk copying to tables that contain sql_variant or bigint data types.

Data_file

The full path to the data file used to bulk copy a table or view to (or from disk). When you bulk copy data to SQL Server, this data file contains the data that will be copied to the specified table or view. When you bulk copy data from a SQL Server, the data file contains the data copied from a table or view. The path can be from 1 to 255 characters.

-m max_errors

Specifies the maximum number of errors that may occur before the bulk copy operation is cancelled. Every line that bcp cannot copy is ignored and counted as an error. If this option is not included, the default is 10.

-f format_file

Specifies the full path to the format file that contains the stored response when bcp was previously used on the same table or view. Use this option when bulk copying in or out of data using a format file created by the format option. The creation of format files is optional. After prompting several formatting questions, bcp prompts you whether to save the answer in the format file. The default file name is Bcp.fmt. When bulk copying data, bcp can reference a format file, so you do not have to re-enter previous answers interactively. If you do not use this option and do not specify-n,-c,-w,-6, or-N, bcp prompts for formatting information.

-e err_file

Specifies the full path to the error file, which stores all lines that bcp cannot transfer from the file to the database. Error messages from bcp are sent to the user's workstation. If this option is not used, no error file is created.

-F first_row

Specifies the ordinal of the first row to bulk copy. The default value is 1, which represents the first line of the specified data file.

-L last_row

Specifies the ordinal of the last row to bulk copy. The default value is 0, which indicates the last line in the specified data file.

-b batch_size

Specifies the number of rows in each batch of data that is copied. Each batch is copied to the server as a transaction. SQL Server commits or rolls back (on failure) each batch transaction. By default, all data in the specified data file is replicated as a batch. Do not use with the-h "ROWS_PER_BATCH = bb" option.

-n

Perform a bulk copy operation using the native (database) data type of the data. This option does not prompt for each field, it uses native values.

-c

Use character data types to perform bulk copy operations. This option does not prompt for each field; it uses char as the storage type without a prefix,\ t (tab) as the field delimiter, and\ n (newline) as the line Terminator.

-N

Use native (database) data types for non-character data and Unicode character types for character data to perform bulk copy operations. This is a higher performance alternative to the-w option, which is designed to use data files to transfer data from one SQL Server to another SQL Server. It does not prompt for each field. You can use this option when you need to transfer data that contains ANSI extended characters and you want to take advantage of the performance of native mode. The-N option cannot be used in SQL Server version 6.5 or earlier.

Important: when bulk copying data from SQLServer to a data file, even if the-VMagneBCP utility is specified, it will not generate a date format of SQLServer 6.0 or SQLServer 6.5 for any datetime or smalldatetime data. The date will always be written in ODBC format. In addition, because SQL Server version 6.5 or earlier does not support nullable bit data, the null value in the bit column is written as a value of 0.

-6

Bulk copy operations are performed using the SQL Server 6.0or SQL Server 6.5data types. Only to maintain backward compatibility. Use the-V option instead.

-C code_page

Only to maintain backward compatibility. Instead, specify a collation name for each column in a format file or interactive bcp.

Specifies the data code page in the data file. Code_page is useful only if the data contains char, varchar, or text columns with character values greater than 127or less than 32.

Code page value description

ACP ANSI/Microsoft Windows? (ISO 1252).

The default code page used by the OEM client. If-C is not specified, this is the default code page used by bcp.

RAW does not convert from one code page to another. Because no conversion occurs, this is the fastest option.

A specific code page number, such as 850.

-r row_term

Specifies the line Terminator. The default line Terminator is\ n (newline). Use this parameter to replace the default line Terminator.

-I input_file

Specify the name of the response file, and when you perform a bulk copy using interactive mode (- n,-c,-w,-6, or-N is not specified), the response file contains a response to each field command prompt question.

-o output_file

Specifies the name of the file that receives the bcp output (redirected from the command prompt).

-a packet_size

Specifies the number of bytes for each network packet sent to and from the server. You can use SQL Server Enterprise Manager (or sp_configure system stored procedures) to set server configuration options. However, you can use this option to override the server configuration option individually. Packet_size can be set to 4096 to 65535 bytes, with a default value of 4096. Increasing packet size can improve the performance of bulk copy operations. If a larger packet is required and is not available, the default setting is used. The performance statistics generated by bcp show the size of the packets used.

-S server_ name [\ instance_name]

Specifies the SQL Server instance to connect to. Specify server_name to connect to the default instance of SQL Server on the server. Specify server_name\ instance_name to connect to the named instance of SQL Server 2000 on the server. If no server is specified, bcp connects to the default instance of SQL Server on the local computer. This option is required when performing bcp from a remote computer on the network.

-T

Specifies that bcp uses the network user's security credentials to connect to the SQL Server through a trust connection. Login_id and password are not required.

-Q

Execute the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and the SQL Server instance. Use this option to specify the name of the database, owner, table, or view that contains spaces or quotation marks. Enclose the entire three-part table or view name in double quotes (").

-w

Use Unicode characters to perform a bulk copy operation. This option does not prompt for each field; it uses nchar as the storage type without a prefix,\ t (tab) as the field delimiter, and\ n (newline) as the line Terminator. Cannot be used in SQL Server version 6.5 or earlier.

-V (60 | 65 | 70)

Use data types from previous versions of SQL Server to perform bulk copy operations. This option is used with the character (- c) or native (- n) format. This option does not prompt for each field, it uses the default value. For example, to bulk copy the date format supported by the bcp utility in SQL Server 6.5 (but no longer supported by ODBC) to SQL Server 2000, use the-V 65 parameter.

-t field_term

Specifies the field Terminator. The default field Terminator is\ t (tab). Use this parameter instead of the default field Terminator.

-U login_id

Specifies the login ID used to connect to the SQL Server.

-P password

Specify the password to log on to ID. If this option is not used, bcp prompts for a password. If you use this option at the end of the command prompt line without a password, bcp uses the default password (NULL).

-v

Report the version number and copyright of the bcp utility.

-R

Specifies that currency, date, and time data are bulk copied to SQL Server using the regional format defined for the locale of the client computer. By default, the locale is ignored.

-k

Specifies that empty columns should leave a null value in a bulk copy operation instead of assigning default values to inserted columns

Thank you for reading this article carefully. I hope the article "what are the parameters of the BCP command" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.