BCP is a command-line tool in SQL Server that is responsible for importing export data

BCP is a command-line tool in SQL Server that is responsible for importing and exporting data, is db-library-based, and can efficiently import and export large quantities of data in a parallel manner. BCP can export a table or view of a database directly, or you can filter a table or view by using the Select from statement. When importing and exporting data, you can use the default values or use a format file to import data from a file into a database or export data from a database to a file.

The following is a detailed discussion of how to import and export data using bcp.

1. Introduction to the main parameters of BCP

BCP has a total of four actions to choose from.

(1) Import.

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

(2) export.

This action is done with the Out command, followed by the name of the file that needs to be exported.

(3) Export using SQL statement.

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

(4) Export format file.

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

Here are some common options:

-F Format_file

Format_file represents the format file name. This option relies on the above action if you are using in or out,format_file to indicate that a format file already exists, and if you are using format, it means 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

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

-L Last_row

Specifies the end of the row to which the exported table is to be directed, or the line to which to import the data from the imported file.

-C

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

-W

Similar to-C, only used when copying data using the Unicode character set, and nchar as the storage type.

-T Field_term

Specifies the character delimiter, which is "\ T" by default.

-R Row_term

Specifies the row separator, which defaults to "\ n".

-S server_name[\instance_name]

Specifies the instance of the SQL Server server to which you want to connect, and if this option is not specified, bcp connects to the native SQL Server default instance. 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 SQL Sever.

-p password

Specifies the user name password to connect to SQL Server.

-T

Specifies that BCP uses a trusted connection to log on to SQL Server. If-T is not specified, you must specify-U and-P.

-K

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

2. 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"--Connect using a password

Or

BCP AdventureWorks.sales.currency out C:\currency1.txt-c-t--Using trusted connections

Here is the output of the above command after execution

Starting copy ...

Copied rows.

Network packet Size (bytes): 4096

Clock time (Ms.) Total:10 Average: (10500.00 rows per sec.)

Here are some of Currency1.txt's content

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 in with a password, you need to double-quote the user name after-U and the password after-p.

Note : In addition to being able to execute in the console, BCP can also run BCP as an SQL statement by calling a system stored procedure of SQL Server xp_cmdshell. 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 return 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 can accept not only table names or view names as parameters, but also SQL as parameters. The SQL statement allows you to filter the tables to be exported and then export the filtered records.

EXEC Master. xp_cmdshell ' BCP ' select TOP $ * from AdventureWorks.sales.currency "queryout c:\currency2.txt-c-U" sa "-P" password "'

BCP can also restrict the exported rows by simply setting options.

EXEC Master. xp_cmdshell ' BCP ' select TOP $ * from AdventureWorks.sales.currency "queryout C:\currency2.txt-F 10-l 13-c-U" sa "-P" p Assword "'

This command uses two parameters-F 10 and-l 13, which means that the results from the Select TOP of the AdventureWorks.sales.currency are taken from 10th to 13 records for export.

BCP is a command-line tool in SQL Server that is responsible for importing and exporting data, is db-library-based, and can efficiently import and export large quantities of data in a parallel manner. BCP can export a table or view of a database directly, or you can filter a table or view by using the Select from statement. When importing and exporting data, you can use the default values or use a format file to import data from a file into a database or export data from a database to a file.

The following is a detailed discussion of how to import and export data using bcp.

1. Introduction to the main parameters of BCP

BCP has a total of four actions to choose from.

(1) Import.

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

(2) export.

This action is done with the Out command, followed by the name of the file that needs to be exported.

(3) Export using SQL statement.

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

(4) Export format file.

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

Here are some common options:

-F Format_file

Format_file represents the format file name. This option relies on the above action if you are using in or out,format_file to indicate that a format file already exists, and if you are using format, it means 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

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

-L Last_row

Specifies the end of the row to which the exported table is to be directed, or the line to which to import the data from the imported file.

-C

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

-W

Similar to-C, only used when copying data using the Unicode character set, and nchar as the storage type.

-T Field_term

Specifies the character delimiter, which is "\ T" by default.

-R Row_term

Specifies the row separator, which defaults to "\ n".

-S server_name[\instance_name]

Specifies the instance of the SQL Server server to which you want to connect, and if this option is not specified, bcp connects to the native SQL Server default instance. 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 SQL Sever.

-p password

Specifies the user name password to connect to SQL Server.

-T

Specifies that BCP uses a trusted connection to log on to SQL Server. If-T is not specified, you must specify-U and-P.

-K

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

2. 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"--Connect using a password

Or

BCP AdventureWorks.sales.currency out C:\currency1.txt-c-t--Using trusted connections

Here is the output of the above command after execution

Starting copy ...

Copied rows.

Network packet Size (bytes): 4096

Clock time (Ms.) Total:10 Average: (10500.00 rows per sec.)

Here are some of Currency1.txt's content

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 in with a password, you need to double-quote the user name after-U and the password after-p.

Note : In addition to being able to execute in the console, BCP can also run BCP as an SQL statement by calling a system stored procedure of SQL Server xp_cmdshell. 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 return 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 can accept not only table names or view names as parameters, but also SQL as parameters. The SQL statement allows you to filter the tables to be exported and then export the filtered records.

EXEC Master. xp_cmdshell ' BCP ' select TOP $ * from AdventureWorks.sales.currency "queryout c:\currency2.txt-c-U" sa "-P" password "'

BCP can also restrict the exported rows by simply setting options.

EXEC Master. xp_cmdshell ' BCP ' select TOP $ * from AdventureWorks.sales.currency "queryout C:\currency2.txt-F 10-l 13-c-U" sa "-P" p Assword "'

This command uses two parameters-F 10 and-l 13, which means that the results from the Select TOP of the AdventureWorks.sales.currency are taken from 10th to 13 records for export.

3. How to export format files using bcp

BCP can not only import and export data based on tables, views, but also work with format files to restrict import and export data. Format files exist as plain text files, and are divided into general and XML formats. Users can write format files by hand, or they can automatically generate format files based on tables and views using bcp commands.

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 the structure of the Currency table, and the following is the contents of this format file.

9.0
3
1 SQLCHAR 0 6 "\ t" 1 CurrencyCode sql_latin1_general_cp1_ci_as
2 SQLCHAR 0 "\ t" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 "\ r \ n" 3 ModifiedDate

This format file records information such as the type, length, character and line delimiter, and field name of the table's Fields (total 3 fields).

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 XML format file describes exactly the same content as the plain format file, except that the format is different.

4. How to import data using bcp

BCP can re-import the Currency1.txt and currency2.txt exported above into the database with the in command, because currency has a primary key, so we will copy a table that is exactly the same as the currency structure.

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 row of records to import data into.

EXEC Master. xp_cmdshell ' BCP AdventureWorks.sales.currency1 in C:\currency1.txt-c-F 10-l 13-t '

When importing data, you can import a record that satisfies a condition into a database based on a format file that already exists and not be imported if it is not satisfied. If the length of the third field in the format file above is 24, if the corresponding field in a text file is longer than 24, then the record will not be imported into the database, and other records satisfying the condition are imported normally.

Use plain format files

EXEC Master. xp_cmdshell ' BCP AdventureWorks.sales.currency1 in C:\currency1.txt-F 10-l 13-c-F C:\currency_format1.fmt-T '

Format file using 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 '

Summarize

The bcp command is a quick data import and export tool provided by SQL Server. Use it to import and export data in an efficient manner without having to start any graphical management tools. Of course, it can also be executed by xp_cmdshell in the SQL statement, which can be placed in the client program (such as Delphi, C #, etc.) to run, which is also a way for the client program to have the data import and export function.

(Editor: Ming Ming mingming_ky#126.com TEL: (010)-68476636)

Original: SQL Server data Import Export Tool bcp detailed

BCP is a command-line tool in SQL Server that is responsible for importing export data