BCP command details

BCP utility in Microsoft? SQL Server? 2000 copy data between an instance and a data file in the format specified by the user.
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]"]

Parameters
Database_name

Name of the database where the specified table or view is located. If not specified, the default database is used.

Owner

Name of the table or view owner. If you have a specified table or view for the large-capacity copy operation, the owner is optional. If you do not specify the owner and do not have the specified table or view, Microsoft? SQL Server? 2000 the system will return an error message and cancel the large-capacity copy operation.

Table_name

The name of the target table when data is copied to SQL Server (in) and the name of the source table when data is copied from SQL Server (out.

View_name

Is the name of the target view for copying data to SQL Server (in) and the name of the source view for (out) Copying data from SQL Server. Only a view with all columns referencing the same table can be used as the target view. For more information about the restrictions on copying data to a view, see insert.

Query

Is a Transact-SQL query that returns a result set. If the query returns multiple result sets, for example, the SELECT statement of the specified 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 a query statement, and use single quotation marks to cause any content embedded in the query statement. You must specify queryout when copying data from a large volume in the query.

In | Out | queryout | format

Specifies the direction of large-capacity replication. In refers to copying from a file to a database table or view, and out refers to copying from a database table or view to a file. Queryout must be specified only when data is replicated from a large capacity in the query. Format creates a format file based on the specified options (-N,-C,-W,-6, or-N) and the table or view separator. If format is used, you must specify the-F option.

Note: The BCP utility in Microsoft SQL Server 6.5 does not support copying large data volumes to tables that contain SQL _variant or bigint data types.

Data_file

The complete path of the data file used to copy a large table or view to a disk (or from a disk. When you copy large data volumes to SQL Server, this data file contains data that will be copied to a specified table or view. When copying data from SQL Server in large capacity, the data file contains data copied from tables or views. The path can contain 1 to 255 characters.

-M max_errors

Specifies the maximum number of errors that may occur before the large-capacity copy operation is canceled. Every row that BCP cannot copy will be ignored and counted as an error. If this option is not included, the default value is 10.

-F format_file

Specifies the full path of the format file. The format file contains the storage response when BCP is used in the same table or view. This option is used when you use a large-capacity copy of the format file created by the format option to copy in or out data. Format File Creation is optional. After several format questions are prompted, BCP will prompt whether to save and answer in the format file. The default file name is BCP. FMT. When copying data in large capacity, BCP can reference a format file, so you do not have to re-enter the previous answer. If this option is not used and-N,-C,-W,-6, or-N is not specified, BCP will prompt you to enter the format information.

-E err_file

Specifies the full path of the error file, which is used to store all rows that BCP cannot transmit from the file to the database. The error message from BCP is sent to the user workstation. If this option is not used, no error file is created.

-F first_row

Specify the ordinal number of the first row to be replicated in large capacity. The default value is 1, indicating the first row of the specified data file.

-L last_row

Specify the ordinal number of the last row to be copied in large capacity. The default value is 0, indicating the last row in the specified data file.

-B batch_size

Specifies the number of rows in each batch of copied data. Each batch is copied to the server as a transaction. SQL Server submits or rolls back each batch of transactions (when a failure occurs. By default, all data in the specified data file is copied as a batch. Do not use it with the-h "rows_per_batch = BB" option.

-N

Use the local (database) Data Type of the data to perform the large-capacity copy operation. This option does not prompt you to enter each field. It uses the local value.

-C

Use the character data type to perform a large-capacity copy operation. This option does not prompt you to enter each field. It uses char as the storage type without a prefix, \ t (Tab) as the field separator, and \ n (line break) as the line terminator.

-W

Use Unicode characters to perform large-capacity copy operations. This option does not prompt you to enter each field. It uses nchar as the storage type without a prefix, \ t (Tab) as the field separator, and \ n (line break) as the line terminator. It cannot be used in SQL Server 6.5 or earlier versions.

-N

The local (database) data type used for non-character data and the Unicode Character Type Used for character data are copied in large capacity. This is a higher performance option that can replace the-W option, with the aim of transferring data from one SQL Server to another using data files. It does not prompt you to enter each field. This option can be used to transmit data that contains ANSI extended characters and to take advantage of the performance of the local mode. You cannot use the-n option in SQL Server 6.5 or earlier versions.

-V (60 | 65 | 70)

Use the data types in earlier versions of SQL Server to perform large-capacity replication. This option is used with the character (-C) or local (-N) format. This option does not prompt you to enter each field. It uses the default value. For example, to copy large data formats supported by the BCP utility in SQL Server 6.5 (but not supported by ODBC) to SQL Server 2000, you can use the-V 65 parameter.

Important to copy data from SQL server to a large data file, even if-V is specified, the BCP utility does not generate a date format of SQL Server 6.0 or SQL Server 6.5 for any datetime or smalldatetime data. The date is always written in ODBC format. In addition, SQL Server 6.5 or earlier versions do not support Empty Bit Data. Therefore, the null value in the bit column is set to 0.

-6

Use the SQL Server 6.0 or SQL Server 6.5 data type to perform a large-capacity copy operation. To maintain backward compatibility. Use the-V option instead.

-Q

Run 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 a database, owner, table, or view that contains spaces or quotation marks. Enclose the entire table name or view name in double quotation marks.

-C code_page

To maintain backward compatibility. In the format file or interactive BCP, specify a sorting rule name for each column.

Data in the specified data fileCodePage. Code_page is useful only when the data contains char, varchar, or text columns whose character values are greater than 127 or less than 32.

Code Page value description
Acp ansi/Microsoft Windows? (ISO 1252 ).
OEM customerProgramThe default code page used. If-C is not specified, this is the default code page used by BCP.
Raw does not convert data from one code page to another. Because no conversion occurs, this is the fastest option.
<Value> a specific code page number, such as 850.

-T field_term

Specifies the field Terminator. The default field Terminator is \ t (Tab ). Use this parameter to replace the default field Terminator.

-R row_term

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

-I input_file

Specify the name of the response file. When the interactive mode (-N,-C,-W,-6, or-N is not specified) is used for large-capacity replication, the response file contains the response to each field of command prompt.

-O output_file

Specifies the name of the file that receives BCP output (redirection from command prompt.

-A packet_size

Specify the number of bytes of each network packet sent to and from the server. You can use the SQL Server Enterprise Manager (or sp_configure system stored procedure) to set server configuration options. However, you can use this option to replace the server configuration option. Packet_size can be set to 4096 to 65535 bytes. The default value is 4096.

Increasing the data packet size can improve the performance of large-capacity replication operations. If a large data packet is required, the default setting is used. The performance statistics generated by BCP show the size of the data packets used.

-S SERVER_NAME [\ instance_name]

Specify the SQL server instance to connect. Specify SERVER_NAME to connect to the default instance of SQL Server on this server. Specify SERVER_NAME \ instance_name to connect to the SQL Server 2000 instance on the server. If no server is specified, BCP is connected to the default instance of SQL server on the local computer. This option is required when a remote computer on the network executes BCP.

-U login_id

The logon ID used to connect to SQL Server.

-P Password

Password of the logon ID. If this option is not used, BCP will prompt you to enter the password. If this option is used at the end of the command prompt line without a password, BCP uses the default password (null ).

-T

Specify that BCP uses the Security creden of network users to connect to SQL Server through a trusted connection. Login_id and password are not required.

-V

Report the version number and copyright of the BCP utility.

-R

Specify the region format defined for the region of the client computer to copy large data volumes of currency, date, and time to SQL Server. By default, the region settings are ignored.

-K

Specify that a null value should be retained for the hollow column in the large-capacity copy operation, instead of assigning the default value to the inserted column.