SQL Server bcp Utility

Server

bcp utility
The bcp utility replicates data in a user-specified format between the Microsoft®sql server™2000 instance and the data file.
Grammar
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

The name of the database that contains the specified table or view. If not specified, the user default database.

Owner

The name of the owner of the table or view. Owner is optional if the user performing the bulk copy operation has the specified table or view. If owner is not specified and the user performing the bulk copy operation does not have the specified table or view, Microsoft®sql server™2000 returns the error message and cancels the bulk copy operation.

table_name

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

View_name

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

Query

is a Transact-SQL query that returns a result set. If a query returns multiple result sets, such as a SELECT statement that specifies a COMPUTE clause, only the first result set is copied to the data file, and subsequent result sets are ignored. Use double quotes to cause a query statement to use single quotes to cause any content embedded in a query statement. When you bulk copy data from a query, you must also specify Queryout.

In | Out | queryout | Format

Specifies the direction of bulk copy. In is copying from a file to a database table or view, out of which you copy from a database table or view to a file. You must specify queryout only when you bulk copy data from a query. Format creates a format file, depending on the option you specify (-N,-C,-W,-6, or-N), and the table or view separator. If you use format, you must also specify the-F option.



Explains 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 of the data file used when bulk copy a table or view to disk (or copy from disk). When bulk copying data to SQL Server, this data file contains data that will be copied to the specified table or view. When you bulk copy data from SQL Server, the data file contains data that is copied from the table or view. The path can have 1 to 255 characters.

-M max_errors

Specifies the maximum number of errors that may occur before the bulk copy operation is canceled. Each row that bcp cannot copy will be ignored and counted as an error. If this option is not included, the default is 10.

-F Format_file

Specifies the full path of the format file that contains the storage responses that were previously used on the same table or view when using bcp. Use this option when bulk copying or copying of data using a format file created by the format option. The creation of a format file is optional. After several formatting questions are prompted, bcp prompts you to save the answer in the format file. The default file name is BCP.FMT. When bulk copying data, BCP can refer to a format file, so you do not have to re-enter the previous answer. If you do not use this option and you do not specify –n,-C,-W,-6, or-N, bcp prompts for the format information.

-E Err_file

Specifies the full path of the error file that is used to store all rows that bcp cannot transfer from the file to the database. Error messages from BCP are sent to the user station. If this option is not used, the error file is not created.

-F First_row

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

-L Last_row

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

-B batch_size

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

-N

Perform bulk copy operations using the native (database) data type of the data. This option does not prompt for each field, and it will use native values.

-C

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

-W

Performs bulk copy operations using Unicode characters. This option does not prompt for each field; it uses nchar as the storage type, without a prefix, \ t (tab) as the field delimiter, \ n (newline character) as the line terminator. cannot be used in SQL Server version 6.5 or earlier.

-N

Performs bulk copy operations on the native (database) data types that use data for non-character data and on character data using Unicode character types. This is a higher performance option than the-W option to transfer data from one SQL Server to another using a data file. It does not prompt for each field. You can use this option when you need to transfer data that contains ANSI extended characters and want to take advantage of native mode performance. You cannot use the-n option in SQL Server version 6.5 or earlier.

-V (60 | 65 | 70)

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



Important When you bulk copy data from SQL Server to a data file, the –V,BCP utility does not generate a SQL Server 6.0 or SQL Server 6.5 date grid for any datetime or smalldatetime data, even if you specify it Expression The date will always be written in ODBC format. Also, since 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

Perform bulk copy operations using SQL Server 6.0 or SQL Server 6.5 data types. only to maintain backward compatibility. Use the –V option instead.

-Q

Executes the SET quoted_identifiers on statement in a connection to a bcp utility and an instance of SQL Server. Use this option to specify the name of the database, owner, table, or view that contains spaces or quotes. Enclose the entire three-part table name or view name in double quotes ("").

-C Code_page

only to maintain backward compatibility. Instead, specify a collation name for each column in the format file or in 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 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 change from one code page to another. This is the quickest option because the conversion does not occur. < value > Specific code page number, for example, 850.

-T Field_term

Specifies the field terminator. The default field terminator is \ t (tab). Use this parameter to override the default field terminator.

-R Row_term

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

-I. Input_file

Specifies the name of the response file that contains a response to a command prompt question for each field when bulk replication is performed using interactive mode (without specifying –n,-C,-W,-6, or-N).

-O output_file

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

-A packet_size

Specifies the number of bytes per network packet sent to and sent 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 server configuration options individually. The packet_size can be set to 4096 to 65535 bytes, and the default value is 4096.

Increased 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 instance of SQL Server to which you want 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 named instance 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.

-U login_id

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

-p password

Specifies the password for the login ID. If this option is not used, bcp prompts for a password. If you use this option with the end of the command prompt without a password, BCP uses the default password (NULL).

-T

Specifies that BCP uses the security credentials of a network user to connect to SQL Server through a trusted connection. No need for login_id and password.

-V

Reports the version number and copyright of the bcp utility.

-R

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

-K

Specifies that empty columns in bulk copy operations should retain a null value instead of assigning a default value to the inserted column.

-E

Specifies that the value of the identity column is present in the file to be imported. If-E is not given, the identity value of this column in the data file being imported is ignored, and SQL Server 2000 automatically assigns a unique value based on the seed and increment values specified during the creation of the table. If the data file's table or view does not contain the value of the identity column, use the format file to specify that the identity column in the table or view should be skipped when the data is imported; SQL Server 2000 automatically assigns a unique value to the column. For more information, see DBCC checkident.

-h "hint [,... n]"

Specifies the hint to use when bulk copying data to a table or view. This option is not available when bulk copying data to SQL Server 6.x or earlier.
Hint Description order (column [ASC | DESC] [,... N]) the sort order of the data in the data file. If the data to be loaded has been sorted according to the clustered index in the table, the performance of the bulk copy is increased. If the data file is sorted in a different order, or if the table does not have a clustered index, the ordering hint is ignored. The column name provided must be a valid column in the destination table. By default, BCP assumes that the data file is not sorted. Rows_per_batch = number of rows of data in BB per batch (that is, BB). Used when no-B is specified, this causes the entire data file to be sent to the server as a single transaction. The server optimizes the bulk load based on the value BB. By default, Rows_per_batch is unknown. Kilobytes_per_batch = the kilobytes (KB) approximate number of data in each batch (that is, CC). By default, Kilobytes_per_batch is unknown. Table-level locks are acquired during TABLOCK bulk copy operations. This hint can significantly improve performance because locks are reduced in the table only during bulk copy operations. If the table is not indexed and TABLOCK is specified, the table can be loaded at the same time by multiple clients. By default, the locking behavior is determined by the Table option table lock on bulk load. During the check_constraints bulk copy operation, all constraints on the destination table are checked. By default, constraints are ignored. Fire_triggers with the in parameter, any insert triggers defined on the destination table are executed during the bulk copy operation. If Fire_triggers is not specified, the INSERT trigger is not executed. For the out, queryout, and format parameters, the fire_triggers is ignored.
Comments
The value of the computed column or timestamp column in the data file to be imported is ignored, and SQL Server 2000 is automatically assigned a value. If the data file does not contain values for computed columns or timestamp columns in the table, the available format files specify that computed columns and timestamp columns in the table should be skipped when the data is imported; SQL Server will automatically assign values to the column.

Computed columns and timestamp columns are copied from SQL Server to a data file as usual.

SQL Server identifiers, including database names, table names, or view names, logins, and passwords, can contain characters such as embedded spaces and quotes. When you specify an identifier or file name that contains a space or quotation mark at a command prompt, you need to enclose the identifier in double quotes (""). In addition, for owners, tables, or views that contain embedded spaces or quotes, you can specify the-Q option or enclose the owner, table, or view name in square brackets ([]) in double quotes.

For example, the Northwind database contains the table Jane ' Orders, which are owned by the user Jane Doe. To use the login Jane Doe and password go dba to bulk copy the table from the Northwind database to the Orders.txt file, execute one of the following commands:

bcp "Northwind.jane doe.jane ' Orders" out "Jane ' s Orders.txt"-c-q-u "Jane Doe"-P "Go dba" bcp ". [Jane Doe]. [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.