SQL BCP command

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.
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, exec master .. xp_mongoshell 'bcp CNT. DBO. t_user out C:/user.txt-c-u "sa"-P "password "'
1. Four actions
01. Import: this operation is completed using the in command, followed by the file name to be imported
02. Export: this operation is completed using the out command, followed by the file name to be exported. The data source is a table or view.
03. Export using an SQL statement: this operation is completed using the queryout command. Similar to the out command, the data source is an SQL statement.
04. Export a format file: this operation is completed using the format command, followed by the Format File Name
2. Common options
01.-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.
02.-x
This option must be used with-F format_file to generate an XML format file.
03.-F first_row
Specifies the row from which the table is exported or the row from which the file is imported.
04.-l last_row
Specifies the end of the exported table or the end of the data imported from the imported file.
05.-C
Char is used as the storage type, with no prefix and "/T" as the field delimiter, and "/N" as the row delimiter.
06.-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.
07.-T field_term
Specifies the delimiter. The default Delimiter is "/t"
08.-R row_term
Specifies the line delimiter. The default value is "/N"
09.-S SERVER_NAME [/instance_name]
Specifies the instance of the SQL Server 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.
10.-U login_id
User name used to connect to SQL Server
11.-P Password
User name and password used to connect to SQL Server
12.-T
Specify bcp to log on to SQL Server using a trusted connection. -U and-P must be specified if-T is not specified
13.-K
Insert a null value for an empty column instead of the default value of this column.
3. Export data
01. Export the entire table or view
Bcp cnt. DBO. t_user out C:/user.txt-c-u "sa"-P "password" -- connect with the password (double quotation marks are required for the user name and password)
Bcp cnt. DBO. t_user out C:/user.txt-c-t -- use a trusted connection
02. Export the target data of the SQL statement
BCP "select top 20 * from ETs. DBO. t_user" queryout C:/user.txt-c-u "sa"-P "password"
BCP "select top 20 * from CNT. DBO. t_user "queryout C:/user.txt-f 10-L 13-c-u" sa "-P" password "-- export 10th to 13 records
03. Export a format file
Bcp cnt. DBO. t_user format NUL-f c:/user_format1.fmt-c-t -- export table structure information to user_format1.fmt
Bcp cnt. DBO. t_user format NUL-f c:/user_format2.fmt-X-c-t -- export table structure information to XML
04. Import Data
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.
Bcp cnt. DBO. t_user in C:/user1.txt-c-t
Bcp cnt. DBO. t_user in C:/user1.txt-C-f 10-L 13-t -- you can also use the-F and-l options to select the target data during import.
Bcp cnt. DBO. t_user in C:/user1.txt-f 10-L 13-C-f c:/user_format1.fmt-t -- use a common format file
Bcp cnt. DBO. t_user in C:/user1.txt-f 10-L 13-C-X-f c:/user_format2.fmt-t -- use an XML Format File
05. Example of actual export
Exec master .. xp_mongoshell 'bcp szc. DBO. t_user out C:/test1.csv-c-u "sa"-P "qwert1" '-- connect with a password
Exec master .. xp_mongoshell 'bcp szc. DBO. t_user out C:/test1.csv-C-t' -- use a trusted connection
4. other methods to import and export data
01. Import Data
Select * into authors1
From OpenDataSource ('sqloledb', 'Data source = 192.1.1.2; userid = sa; Password = qwert1 '). Pubs. DBO. Authors
02. Export data
Insert into OpenDataSource ('sqloledb', 'Data source = 192.1.1.2; userid = sa; Password = qwert1 '). Test. DBO. Authors
Select * from pubs. DBO. Authors
Insert into OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'text; database = C:/') [data # TXT] Select * From text1
Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'text; database = C:/', [data # TXT]) Select * From text1
03. Import Excel Data
Select * into EXCEL from OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'excel 5.0; database = C:/book1.xls') [sheet1 $]
04. Import to excel
Insert into OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'excel 5.0; database = C:/book1.xls') [sheet1 $] Select * from Excel