SQL Server bcp (data import and export tool) General Usage and command details

Bcp is a command line tool used in SQL Server to import and export data. It is based on DB-Library and can efficiently import and export large batches of data in parallel. Bcp can be used to export tables or views of a database, or filter tables or views using the select from statement to export them. When importing and exporting data, you can use the default value or use a format file to import the data in the file to the database or export the data in the database to the file. The following describes how to use bcp to import and export data.

1. Introduction to main bcp Parameters

Bcp has four actions to choose from.
(1) import.
This operation is completed using the in command, followed by the file name to be imported.
(2) Export.
This operation is completed using the out command, followed by the file name to be exported.
(3) use SQL statements to export data.
This operation is completed using the queryout command. It is similar to out, but the data source is not the table or view name, but an SQL statement.
(4) export the format file.
This operation is completed using the format command, followed by the format file name.
The following describes some common options:
-F format_file
Format_file indicates the format file name. This option depends on the preceding action. If in or out is used, format_file indicates an existing format file. If format is used, it indicates a format file to be generated.
-X
This option must be used with-f format_file to generate an xml format file.
-F first_row
Specifies the row of the exported table or the row of the imported file.
-L last_row
Specifies the end of the exported table or the end of the imported data.
-C
Char is used as the storage type, with no prefix and "\ t" as the field delimiter, and "\ n" as the row delimiter.
-W
Similar to-c, it is used only when Unicode character sets are used to copy data, and nchar is used as the storage type.
-T field_term
Specifies the delimiter. The default Delimiter is "\ t ".
-R row_term
Specifies the line delimiter. The default value is "\ n ".
-S server_name [\ instance_name]
Specifies the instance of the SQL Server to be connected. If this option is not specified, bcp connects to the default instance of the local SQL Server. To connect to the default instance on a machine, you only need to specify the machine name.
-U login_id
Specifies the user name used to connect to the SQL server.
-P password
Specify the username and password used to connect to SQL Server.
-T
Specify bcp to log on to SQL Server using a trusted connection. -U and-P must be specified if-T is not specified.
-K
The null value is used to insert an empty column, rather than the default value of this column.

2. How to Use bcp to export data

(1) Use bcp to export the entire table or view.
Bcp AdventureWorks. sales. currency out c: \ currency1.txt-c-U "sa"-P "password" -- connect with a password or bcp AdventureWorks. sales. currency out c: \ currency1.txt-c-T -- use a trusted connection
The output result after the preceding command is executed is as follows:
Starting copy...
105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total: 10 Average: (10500.00 rows per sec .)
The following is part of currency1.txt.
AED Emirati Dirham 1998-06-01 00:00:00. 000
AFA Afghani 1998-06-01 00:00:00. 000
.........
.........
ZWD Zimbabwe Dollar 1998-06-01 00:00:00. 000
When you log on with a password, you need to add double quotation marks to the user name after-U and the password after-P.
Note: In addition to running bcp on the console, you can also run bcp using SQL statements by calling a system stored procedure xp_cmdshell of SQL Server. For example, the first command can be rewritten
EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency out c: \ currency1.txt-c-U "sa"-P "password "'
After xp_cmdshell is executed, the returned information is output as a table. To facilitate the execution of bcp in SQL, the following commands use xp_cmdshell to execute the bcp command.
(2) filter the table to be exported.
Bcp not only accepts table or view names as parameters, but also SQL as parameters. You can use SQL statements to filter the tables to be exported and then export the filtered records.
EXEC master.. xp_mongoshell 'bcp "select top 20 * FROM AdventureWorks. sales. currency" queryout c: \ currency2.txt-c-U "sa"-P "password "'
Bcp can also restrict exported rows by simply setting options.
This command uses two parameters-F 10 and-L 13 to indicate
EXEC master .. xp_cmdshell 'bcp "select top 20 * FROM AdventureWorks. sales. currency "queryout c: \ currency2.txt-F 10-L 13-c-U" sa "-P" password "'
Lect top 20 * FROM AdventureWorks. sales. currency, 10th to 13 records are retrieved for export.

3. How to Use bcp to export a format file

Bcp not only allows you to import and export data based on tables and views, but also limits the import and export data with format files. A format file exists as a plain text file, which is in the general format and xml format. You can manually write a format file or use the bcp command to automatically generate a format file based on tables and views.
EXEC master .. xp_mongoshell 'bcp AdventureWorks. sales. currency format nul-f c: \ currency_format1.fmt-c-t'
The above command generates a format file currency_format1.fmt for the structure of the currency table. The content of this format file is as follows.
9.0
3
1 SQLCHAR 0 6 "\ t" 1 CurrencyCode SQL _Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\ t" 2 Name SQL _Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 "\ r \ n" 3 ModifiedDate
This format file records the type, length, character, row delimiter, and field name of the table field (three fields in total.
Bcp can also generate xml format files using the-x option.
EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency format nul-f c: \ currency_format2.fmt-x-c-t'
The content described in an xml format file is exactly the same as that described in a common format file, but the format is different.

4. How to Use bcp to import data

Bcpcan use the InCommand to re-import the currency1.txtand currency2.txt imported by the role to the database. Because currency has a primary key, we will copy a table with the same structure as currency.
Select top 0 * INTO AdventureWorks. sales. currency1 FROM AdventureWorks. sales. currency
Import data to the currency1 table
EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-c-t'
You can also use the-F and-L options to select the record rows for data import.
EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-c-F 10-L 13-t'
When importing data, you can import qualified records to the database based on existing format files. For example, the character length of the Third Field in the preceding format file is 24. If the length of the corresponding field in a text file exceeds 24, this record will not be imported into the database, other matching records are imported normally.
Use common format files
EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-F 10-L 13-c-f c: \ currency_format1.fmt-t'
Use an xml Format File
EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-F 10-L 13-c-x-f c: \ currency_format2.fmt-t'

Summary

The bcp command is a quick data import and export tool provided by SQL Server. You can use it to import and export data in an efficient manner without starting any graphical management tool. Of course, it can also be executed in SQL statements through xp_cmdshell. In this way, it can be run in a client program (such as delphi and c, this is also one of the ways to enable the client program to import and export data.

Sybase)

The bcp utility copies data between a Microsoft SQL Server 2000 instance and a data file in a user-specified format.
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.

Specifies the data code page in the data file. 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 ).
The default code page used by the OEM Program. 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.

Common bcp usage

Usage: bcp {dbtable | query} {in | out | queryout | format} Data File

[-M maximum error count] [-f format file] [-e error file]
[-F First line] [-L last line] [-B batch size]
[-N local type] [-c character type] [-w width character type]
[-N keeps non-text as the local type] [-V file format version] [-q quoted identifier]
[-C code page description] [-t field Terminator] [-r line terminator]
[-I input file] [-o output file] [-a packet size]
[-S server name] [-U user name] [-P Password]
[-T trusted connection] [-v version] [-R allows region settings]
[-K reserved null value] [-E reserved id value]
[-H "loading prompt"] [-x generates an xml format file]
Import csv files
Exec master.. xp_mongoshell 'bcp "SSIS. dbo. tb2" in "E: \ export.csv"-c-t ","-r "\ n"-t'
Export to csv
Exec master.. xp_mongoshell 'bcp "SSIS. dbo. tb2" out "E: \ test.csv"-c-t ","-r "\ n"-t'
Export a specific query to the default format
By default, it is separated by tabs and "\ n" is used for line feed.
Exec master .. xp_mongoshell 'bcp "select carbrand, longpolling from ssis. dbo. tb2" queryout "E: \ test2.txt"-c-t'
Other cases reference: http://msdn.microsoft.com/zh-cn/library/ms162802.aspx
Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp _ Your shell', 1
GO
RECONFIGURE
GO