Use BCP to import and export large-capacity data

This section describes how to use the following four examples: Export (out), import (in), query and export (queryout), and export and format (-f) files.
Example 1: export the entire table currency or view of the sample database AdventureWorks, and use SQL to connect

Copy codeThe Code is as follows: bcp AdventureWorks. sales. currency out c: \ currency1.txt-c-U "sa"-P "password" -- Connect using SQL

Example 2: connect with a trusted connection.

Copy codeThe Code is as follows: bcp AdventureWorks. sales. currency out c: currency1.txt-c-T -- use a trusted connection

Example 3: import data

Copy codeThe Code is as follows: bcp AdventureWorks. sales. currency1 in c: currency1.txt-c-T

Or import 10-13 rows of the specified data

Copy codeThe Code is as follows: bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-c-F 10-L 13-T

Example 4: Use query to export data

Copy codeThe Code is as follows: bcp "select top 20 * FROM AdventureWorks. sales. currency "queryout c: \ currency2.txt-F 10-L 13-c-U" sa "-P" password "-S" IP"

Or

Copy codeThe Code is as follows: bcp "select top 4 * FROM AdventureWorks. sales. currency where id not in (select top 9 id FROM AdventureWorks. sales. currency "queryout c: \ currency2.txt-c-U" sa "-P" password "-S" IP"

Example 5: export a formatted file:

Copy codeThe Code is as follows: EXEC master .. xp_mongoshell 'bcp AdventureWorks. sales. currency format nul-f c: \ currency_format1.fmt-c-T

Finally: The above is the BCP tool of the client, you can also use the BCP of the server from the client

Copy codeThe Code is as follows: EXEC master .. xp_mongoshell 'bcp "select top 20 * FROM AdventureWorks. sales. currency" queryout c: \ cur.txt-F 10-L 13-c-T