BCP utility
The BCP utility replicates data in the format specified by the Microsoft® SQL ServerTM 2000 instance and data files.
grammar
BCP {[[Database_name] [Owner].] {Table_name | View_name} | "query"} {in | out | queryout | format} Data_File [-m max_erroat} [-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 infut_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
Database_name
The name of the database where the specified table or view is located. If not specified, the user defaults the database.
Owner
The name of the table or view owner. If the user who performs a large-capacity replication operation has a specified table or view, Owner is optional. If you do not specify Owner and perform a large-capacity replication operation does not have a specified table or view, Microsoft® SQL ServerTM 2000 will return an error message and cancel a large-capacity replication operation.
Table_name
It is a destination table name that copies the data to the SQL Server (IN), and the source table name when copying data (OUT) from SQL Server.
View_name
It is the source view name that copies the data to the SQL Server (IN), and the source view name when copying data from SQL Server (OUT). Only the views of the same table can be used as the destination view. See INSERT for more information on copying data to the limits of the view.
Query
Is the Transact-SQL query that returns a result set. If the query returns multiple result sets, such as specifying the SELECT statement of the compute clause, only the first result set is copied to the data file, and the subsequent result set is ignored. Use dual quotes to cause query statements, using single quotes to cause anything embedded in the query statement. When copying data from the query, you must also specify Queryout.
IN | OUT | Queryout | Format
Specifies the direction of mass replication. IN is copy from the file to the database table or view, and OUT refers to the file from the database table or view. Queryout must only be specified when copying data from a large capacity in the query. Format will create a format file based on the specified option (-n, -c, -w, -6 or -n) and table or view separator. If you use Format, you must also specify the -f option.
Note The BCP utility in Microsoft SQL Server 6.5 does not support large-capacity copies to tables containing SQL_VARIANT or BIGINT data types.
Data_file
The full path to the data file is used when a large-capacity replicate table or view is used to disk (or copy from disk). This data file contains data that will be copied to the specified table or view when copying the data large capacity to SQL Server. When data is replicated from SQL Server, the data file contains data replicated from the table or view. The path can have 1 to 255 characters.
-M Max_ERRORS
Specifies the maximum number of errors that may be generated before the mass replication operation is canceled. Each row that BCP cannot be copied will be ignored and is an error. If this option is not included, it is default that 10. -f format_file
Specifies the full path to the format file, which contains a storage response when using BCP before the same table or view. This option is used when using the format file created by the Format option to copy or copy data. The creation of format files is optional. After prompting a few format issues, the BCP will prompt to save an answer in the format file. The default file is named bcp.fmt. When you copy data, BCP can reference a format file, so you don't have to re-interact forward the previous answer. If this option is not used, the -n, -c, -w, -6 or -n is specified, and the BCP will prompt the input format information.
-E Err_File
Specify the full path to the error file, this error file is used to store BCP to transfer from file to all rows of the database. Error information from BCP will be sent to the user workstation. If this option is not used, the error file is not created.
-F first_row
Specifies the number of orders of the first row to be copied. The default is 1, indicating the first line of the specified data file.
-L Last_Row
Specifies the order of the last line to be copied. The default is 0, indicating the last line in the specified data file.
-b Batch_size
Specifies the number of rows in each batch of data copied. Each batch is copied as a transaction to the server. SQL Server submits or rolls back (when failure), each batch transaction. By default, all data in the specified data file are copied as a batch. Please do not use with the -h "rows_per_batch = bb" option.
NN
This unit (database) data type using data is used to perform large-capacity replication operations. This option is not prompted to enter each field, which will use this machine.
-C
Perform a large-capacity replication operation using the character data type. This option does not prompt to enter each field; it uses char as a storage type, without prefix, / t (tab) as a field separator, / n (newline) as a row terminator.
-w
Execute a large-capacity replication operation using the Unicode character. This option is not prompted to enter each field; it uses nchar as a storage type, without prefix, / t (tab) as a field separator, / n (newline) as a row terminator. Can't be used in SQL Server 6.5 or earlier.
NN
This unit (database) data type for non-character data is used, and the character data is used to perform a large-capacity replication operation using the Unicode character type. This is a higher performance option that can replace -w options, which is to transfer data from one SQL Server to another using data files. It does not prompt to enter each field. This option can be used when data that needs to be transmitted containing an ANSI extended character and want to utilize the performance of this machine mode. The -n option cannot be used in SQL Server 6.5 or earlier.
-V (60 | 65 | 70)
Execute a large-capacity replication operation using the data type in your early versions of SQL Server. This option is used with characters (-C) or native (-n) format. This option does not prompt to enter each field, which uses the default value. For example, if you want to copy the date format of the BCP utility in SQL Server 6.5 (but ODBC no longer support) to SQL Server 2000, the -v 65 parameters can be used.
Important When you copy data from SQL Server to a data file, even if you specify -V, BCP utility does not generate SQL Server 6.0 or SQL Server 6.5's date format for any DateTime or SmallDateTime data. Dates will always be written in ODBC format. In addition, since SQL Server 6.5 or earlier does not support Bit data, the null value in the bit column is 0. -6
Perform a large-capacity replication operation using SQL Server 6.0 or SQL Server 6.5 data type. Only for keep backward compatibility. Change to use the -v option.
-Q
Execute a set quoted_identifiers ON statement in the connection of the BCP utility and SQL Server instance. Use this option to specify the name of the database, owner, table, or view containing spaces or quotes. The entire table name or view name consisting of three parts is in double quotes ("").
-C code_page
Only for keep backward compatibility. Instead, specify a sort rule name for each column in the format file or interactive BCP.
Specify the data code page in the data file. CODE_PAGE is only useful when the data contains a character value greater than 127 or less than 32.
Code page value Description ACPANSI / 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 take the conversion from one code page to another code page. This is the fastest option because there is no conversion.
-t field_Term
Specify 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 row terminator is / n (newline). Use this parameter to replace the default line terminator.
-i input_file
Specifies the name of the response file, when performing large-capacity replication using interactive mode (not specified - N, -C, -W, -W, -6 or -N), the response file contains a response to each field command prompt problem.
-o output_file
Specifies the name of the file that receives the BCP output (from the command prompt redirection).
-a packet_size
Specifies the number of bytes that sends to and send each network packet of the server. You can set the server configuration option using the SQL Server Enterprise Manager (or SP_CONFIGURE system stored procedure). However, use this option to replace the server configuration option in a single place. Packet_size can be set to 4096 to 65535 bytes, the default is 4096.
The increase in data packet size can improve the performance of a large capacity replication operation. If a large packet is required, the default setting is used. BCP generated performance statistics 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 SQL Server default instance on the server. Specify server_name / instance_name to connect to the SQL Server 2000 name instance on the server. If a server is not specified, BCP is connected to the SQL Server default instance on the local computer. This option is required when performing BCP from a remote computer on the network.
-U login_id
Specifies the login ID for connecting to SQL Server.
-P Password
Specifies the password of the login ID. If this option is not used, the BCP will prompt the password. If you do not have a password, this option is used to command the end of the line, and the BCP will use the default password (NULL).
-T
Specify BCP to connect to SQL Server through a trust connection using the security credentials of the network user. No login_id and password. -V
Report the version number and copyright of the BCP utility.
-R
Specify the area format defined for the area settings for the client computer, copy the money, date, and time data to the SQL Server. The area settings will be ignored by default.
-k
Specifies that a null value should be retained in a large-capacity replication operation, not the default value to the inserted column.
-E
The value of the specified identity column appears in the file to be imported. If no -e is given, the identifier value of this column will be ignored, and SQL Server 2000 automatically assigns unique values based on the seed value and increment value specified during the creation table. If the value of the identity column is not included in the table or view of the data file, you should skip the identity columns in the table or view when you specify the import data; SQL Server 2000 will automatically assign the column assigned a unique value. See DBCC Checkident for more information.
-H "hint [, ... n]"
Specifies the prompt used when large-capacity replication data to the table or view. This option cannot be used when you copy data to SQL Server 6.x or earlier.
Tip Description ORDER (Column [ASC | DESC] [, ... N]) data file sort order. If the data to be loaded has been sorted according to the aggregation index in the table, the performance of the large capacity replication will be improved. If the data file is sorted in different order, or the ORDER prompt will be ignored if the table does not have an index. The column name provided must be a valid column in the target table. By default, BCP assumes that the data file is not sorted. Rows_per_batch = bb number of rows (ie BB) per batch. Using -B is not specified, this will send the entire data file as a single transaction to the server. The server optimizes large capacity load according to value BB. By default, rows_per_batch is unknown. Kilobytes_Per_batch = CC A thousand bytes (KB) of each batch (ie CC). By default, kilobytes_per_batch is unknown. A table-level lock will be obtained during the TabLock large-capacity replication operation. Since the control lock is reduced by only the championship of the table, this tip can significantly improve performance. If the table has no index and specifies TabLock, the table can be loaded simultaneously by multiple clients. By default, the lock behavior is determined by the table option Table Lock on bulk load. CHECK_CONSTRAINTS During the large-capacity replication operation, all constraints on the table will be checked. By default, constraints will be ignored. Fire_triggers specify together with the IN parameter, any insert trigger defined on the target table will be executed during a large capacity replication operation. If Fire_Triggers are specified, the inserted trigger is not executed. For OUT, Queryout and Format parameters, Fire_triggers will be ignored.
Comment
The value of the calculated column or TimeStAMP column will be ignored in the data file to be imported, and the SQL Server 2000 automatically assigns. If the data file does not include the value of the calculated column or TimeStAMP column, the available format file specifies the calculation column and the TimeStAMP column that should be skipped in the table when importing data; SQL Server will automatically assign the column.
Calculating columns and TimeStamp columns often copied from SQL Server to a data file.
SQL Server identifiers (including database names, table names, or view names, login, and passwords) can include characters such as embedded spaces and quotes. When the identifier or file name containing a space or quotation is specified at the command prompt, it is necessary to cross the identifier in double quotes (""). In addition, for the name of the owner, table or view of the embedded space or quotation, the -Q option can be specified, or the name of the owner, the table, or the view is enclosed in both brackets ([]) in double quotes. For example, there is a table Jane's ORDERS in the Northwind database, which is owned by the user Jane Doe. To use the login Jane DoE and password Go DBA to copy the table from the NorthWind database to the Orders.txt file, perform one of the following commands:
BCP "Northwind.jane Doe.jane's Orders" OUT "Jane's ORDERS.TXT" -c -q -u "jane doe" -p "go dba"
BCP "Northwind. [Jane's Orders]" Out "Jane's Orders.txt" -c -u "jane doe" -p "go dba"
To specify a database name that contains spaces or quotes, you must use the -q option.
