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

Editor to share with you how to use the SQL Server data import and export tool BCP. I hope you will get something after reading this article. Let's discuss it together.

BCP is a command line tool responsible for importing and exporting data in SQL Server. It is based on DB-Library and can import and export large quantities of data efficiently in parallel. BCP can export the tables or views of the database directly, or filter the tables or views through SELECT FROM statements. When you import and export data, you can use the default values or use a format file to import the data from the file into the database or export the data from the database to a file.

How to import and export data using BCP is discussed in detail below.

1. Introduction of main parameters of BCP

There are four actions to choose from in BCP.

(1) Import.

This action is done using the in command, followed by the name of the file to be imported.

For example:

Bcp sxculture.dbo.show_t_type in c:\ temp\ show_t_type-U sa-P 111111-Szhouyx-c-Jcp850

(2) Export.

This is done using the out command, followed by the name of the file to be exported.

For example:

Bcp sxculture.dbo.Cul_MuseumData out d:\ Cul_MuseumData_out-Usa-P111111-Szhouyx-c

(3) use SQL statement to export.

This action is done using the queryout command, which is similar to out, except that the data source is not the table or view name, but the SQL statement.

(4) Export the format file.

This action is done using the format command, followed by the format file name.

Here are some common options:

-f format_file

Format_file represents the format file name. This option depends on the above actions, if you are using in or out,format_file to represent an existing format file, and if you are using format to indicate the format file to be generated.

-x

This option is used in conjunction with-f format_file to generate a format file in xml format.

-F first_row

Specify which row of the exported table to export from, or which row of the imported file to import.

-L last_row

Specify the line to which the exported table ends, or when you import data from the imported file.

-c

When logging in with a password, you need to enclose the user name after-U and the password after-P in double quotes.

Note: in addition to being executed on the console, BCP can also run BCP as a SQL statement by calling xp_cmdshell, a system stored procedure of SQL Server. If the first command above can be rewritten as

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:currency1.txt-c-U "sa"-P "password"'

After the xp_cmdshell is executed, the returned information is output as a table. In order to easily execute BCP in SQL, the following commands use xp_cmdshell to execute the BCP command.

(2) filter the table to be exported.

BCP can accept not only the table name or view name as a parameter, but also SQL as a parameter. The SQL statement allows you to filter the table to be exported, and then export the filtered record.

EXEC master..xp_cmdshell '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.

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"'

This command takes two parameters-F 10 and-L 13, indicating that the 10th to 13th records are exported from the results found by SELECT TOP 20 * FROM AdventureWorks.sales.currency.

3. How to use BCP to export format files

BCP can not only import and export data according to tables and views, but also restrict import and export data with format files. Format files exist as plain text files, which are divided into general format and xml format. Users can write format files manually or automatically generate format files according to tables and views through the BCP command.

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul-f c:currency_format1.fmt-c-T'

The above command generates a format file currency_format1.fmt from the structure of the currency table. Here is the content of the format file.

9.0

three

1 SQLCHAR 0 6 "" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 100 "" 2 Name SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 24 "" 3 ModifiedDate

This format file records the table's field (a total of three fields) type, length, characters and line separators and field names.

BCP can also generate format files in xml format with the-x option.

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul-f c:currency_format2.fmt-x-c-T'

The content described by the xml format file is exactly the same as that described by the normal format file, but in a different format.

4. How to import data using BCP

BCP can re-import the currency1.txt and currency2.txt exported above into the database through the in command, and since currency has a primary key, we will copy a table that has exactly the same structure as currency.

SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency

Import data into the currency1 table

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt-c-T'

Importing data can also use the-F and-L options to select the record rows for the imported data.

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt-c-F 10-L 13-T'

When importing data, the records that meet the criteria can be imported into the database according to the existing format file, but not if it is not satisfied. If the character length of the third field in the above format file is 24, if the length of the corresponding field in a text file exceeds 24, the record will not be imported into the database, and other records that meet the conditions will be imported normally.

Use files in a normal format

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt-F 10-L 13-c-f c:currency_format1.fmt-T'

Use format files in xml format

EXEC master..xp_cmdshell '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. It can be used to import and export data in an efficient manner without starting any graphical management tools. Of course, it can also be executed in SQL statements through xp_cmdshell, which can be run in client programs (such as delphi, c #, etc.), which is also one of the ways to enable client programs to import and export data.

Use the char type as the storage type, without a prefix and with "" as the field separator and "" as the line separator.

-w

Similar to-c, except that it is used when copying data using the Unicode character set and uses nchar as the storage type.

-t field_term

Specifies the character separator, which defaults to "".

-r row_term

Specifies the line separator, which defaults to "".

-S server_name [instance_name]

Specifies the instance of the SQL Server server to connect to, and if this option is not specified, BCP connects to the default instance of SQL Server on this machine. If you want to connect to the default instance on a machine, you only need to specify the machine name.

-U login_id

Specifies the user name to connect to the SQL Sever.

-P password

Specifies the username and password to connect to the SQL Server. -www.bianceng.cn

-T

Specifies that BCP logs in to SQL Server using a trusted connection. If-T is not specified,-U and-P must be specified.

-k

Specifies that an empty column is inserted with a null value instead of the default value for this column.

two。 How to export data using BCP

(1) use BCP to export the entire table or view.

BCP AdventureWorks.sales.currency out c:currency1.txt-c-U "sa"-P "password"-use password to connect

Or

BCP AdventureWorks.sales.currency out c:currency1.txt-c-T-use a trusted connection

The following is the output after the above command is executed

Starting copy...

105 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total: 10 Average: (10500.00 rows per sec.)

Here are some of the contents of currency1.txt

AED Emirati Dirham 1998-06-01 0014 00.000

AFA Afghani 1998-06-01 0014 00.000

...

...

ZWD Zimbabwe Dollar 1998-06-01 0014 00.000

After reading this article, I believe you have a certain understanding of "how to use SQL Server data Import and Export tool BCP". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for your reading!

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.