Powerful SQL command set for Data Replication-knowledge point BCP 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.

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 uses two parameters-f 10 and-L 13, which indicate that from select top 20 * From adventureworks. sales. in the result obtained by currency, 10th to 13 records are obtained 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.