SQL Server BCP usage Summary

Usage: BCP {dbtable | query} {in | Out | queryout | format} Data File
[-M maximum error count] [-F format file] [-e error file]
[-F First line] [-l last line] [-B batch size]
[-N local type] [-C character type] [-W width character type]
[-N keeps non-text as the local type] [-V file format version] [-Q quoted identifier]
[-C code page description] [-T field Terminator] [-R line terminator]
[-I input file] [-O output file] [-a packet size]
[-S server name] [-u user name] [-P Password]
[-T trusted connection] [-V version] [-R allows region settings]
[-K reserved null value] [-e reserved id value]
[-H "loading prompt"] [-x generates an XML format file]

-- Do not import or export formatted files (the-S parameter is omitted in the following example, because it is on the local machine and the default instance)
-- Simple export table
Exec master .. xp_mongoshell 'bcp db_oa.dbo.t_user_list out c: \ a.xls-C-t' -- "-T" Trust connection
Exec master .. xp_mongoshell 'bcp db_oa.dbo.t_user_list out c: \ a.txt-C-USA-pxxxxx'
-- Filter the exported table
Exec master .. xp_mongoshell 'bcp "select top 3 * From db_oa.dbo.t_user_list" queryout c: \ a.txt-C-t'
-- Simple import table
Exec master .. xp_mongoshell 'bcp db_oa.dbo.t_user_list_2 in C: \ a.txt-C-t'
Exec master .. xp_mongoshell 'bcp db_oa.dbo.t_user_list_2 in C: \ a.txt-c-t-e '-- add "-e" to enable identity_insert on

-- The following describes how to import data using an XML formatted file!
-- Create a test table
Use db_oa;
Go
Create Table mytestformatfiles (
Col1 smallint,
Col2 nvarchar (50 ),
Col3 nvarchar (50 ),
Col4 nvarchar (50)
);
Go

-- Output XML formatted File
-- Description:-T "," specifies the field separator. We will talk about it later.
Exec master.. xp_mongoshell 'bcp db_oa .. mytestformatfiles format NUL-c-t ","-X-f c: \ mytestformatfiles. XML-t'

/*
The formatted file content is as follows:

<? XML version = "1.0"?>
<Bcpformat xmlns = "http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance">
<Record>
<Field ID = "1" xsi: TYPE = "charterm" Terminator = "," max_length = "7"/>
<Field ID = "2" xsi: TYPE = "charterm" Terminator = "," max_length = "100" collation = "chinese_prc_ci_as"/>
<Field ID = "3" xsi: TYPE = "charterm" Terminator = "," max_length = "100" collation = "chinese_prc_ci_as"/>
<Field ID = "4" xsi: TYPE = "charterm" Terminator = "\ r \ n" max_length = "100" collation = "chinese_prc_ci_as"/>
</Record>
<Row>
<Column source = "1" name = "col1" xsi: TYPE = "sqlsmallint"/>
<Column source = "2" name = "col2" xsi: TYPE = "sqlnvarchar"/>
<Column source = "3" name = "col3" xsi: TYPE = "sqlnvarchar"/>
<Column source = "4" name = "col4" xsi: TYPE = "sqlnvarchar"/>
</Row>
</Bcpformat>

Here we will explain the keyword of the file:
<Record> the content in the parent node is the format definition of the source file, that is, the data file to be imported.
<Field ID = "1" (here is the source data field ID) xsi: TYPE = "charterm" (field type) Terminator = "," (field separator) max_length = "7" (max length)/>
It is not hard to see that the most common change is Terminator, which can be changed to "|" when you need the separator, note that the last field separator is "\ r \ n"
</Record>
<Row> the content of the parent node is the format definition of the target data, that is, the place where you need to import the data file.
<Column source = "1" (this is very important. It specifies the ID of the field in the source data above. When the order or number of fields in the source data and the target data are inconsistent, we can modify the result here to achieve the desired value.) name = "col1" (target field name) xsi: TYPE = "sqlsmallint" (target field type)/>
</Row>
*/
-- Next we create the source data file, paste the following content into the data file we specified c: \ myTestFormatFiles-c.txt
/*
10, field2, field3, field4
15, field2, field3, field4
46, field2, field3, field4
58, field2, field3, field4
*/
-- Use bcp to format a file and import data
Exec master.. xp_cmdshell 'bcp db_oa .. mytestformatfiles in c: \ myTestFormatFiles-c.txt-f c: \ mytestformatfiles. XML-t'
-- View the imported data
Select * From mytestformatfiles
/*
Col1 col2 col3 col4
10 field2 field3 field4
15 field2 field3 field4
46 field2 field3 field4
58 field2 field3 field4
*/

-- Use bulk insert to import data
Use db_oa;
Go
Delete mytestformatfiles;
Go
Bulk insert mytestformatfiles
From 'C: \ myTestFormatFiles-c.txt'
With (formatfile = 'C: \ mytestformatfiles. xml ');
Go
Select * From mytestformatfiles;
Go
/*
Col1 col2 col3 col4
10 field2 field3 field4
15 field2 field3 field4
46 field2 field3 field4
58 field2 field3 field4
*/

-- Import using OpenRowSet large-capacity row set provider
Use db_oa;
Delete mytestformatfiles;
Go
Insert into mytestformatfiles
Select *
From OpenRowSet (bulk 'C: \ myTestFormatFiles-c.txt ',
Formatfile = 'C: \ mytestformatfiles. xml'
) As T1;
Go
Select * From mytestformatfiles;
Go
/*
Col1 col2 col3 col4
10 field2 field3 field4
15 field2 field3 field4
46 field2 field3 field4
58 field2 field3 field4
*/

-- Delete the test table
Drop table mytestformatfiles