SQL Server bcp uses small closing

Code highlighting produced by Actipro Codehighlighter (freeware) http://www.codehighlighter.com/-->
Usage: bcp {dbtable | query} {in | out | queryout | format} data file
[-m maximum number of errors] [-F Format File] [-e Error file]
[-F First line] [-L-End line] [-B Batch Size]
[-N Native Type] [-C character type] [-W wide character type]
[-N to keep non-text as native type] [-V file format version] [-Q quoted identifier]
[-C code page specifier] [-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 locale settings]
[-K reserved NULL] [-e reserved Identity value]
[-H ' load hint '] [-x generate XML format file]

--does not take advantage of the import and export of a format file (the following example omits the-s parameter, because it is native and is the default instance)
--Simple export table
EXEC master. xp_cmdshell ' bcp db_oa.dbo.T_User_list out c:\a.xls-c-t '--"t" trusted connection
EXEC master. xp_cmdshell ' bcp db_oa.dbo.T_User_list out c:\a.txt-c-usa-pxxxxx '
--Filter Export tables
EXEC master. xp_cmdshell ' bcp ' select Top 3 * from Db_oa.dbo.t_user_list "queryout c:\a.txt-c-T"
--Simple Import table
EXEC master. xp_cmdshell ' bcp db_oa.dbo.T_User_list_2 in c:\a.txt-c-T '
EXEC master. xp_cmdshell ' bcp db_oa.dbo.T_User_list_2 in c:\a.txt-c-t-e '--plus '-e ' enable IDENTITY_INSERT on

--The following is the use of XML format file for data import!
--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 format file
-------T "," is the specified field delimiter and we'll talk about it later
EXEC master. xp_cmdshell ' bcp Db_oa. myTestFormatFiles format nul-c-T ","-x-f C:\myTestFormatFiles.Xml-T "

/*
The contents of the formatted file are 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 explain the file keyword a little:
<RECORD> the content in the parent node is a formatted definition of the source file, that is, the data file that you want to import
<field id= "1" (here is the source data field ID) xsi:type= " Charterm "(field type) terminator=", "(Field delimiter) max_length=" 7 "(maximum length)/>
It is not difficult to see that the most common modification is TERMINATOR, can be modified to" | " Wait for the delimiter you want, note that the delimiter for the last field is "\ r \ n"
</record>
<ROW> the content of the parent node is the formatted definition of the target data, that is, where you need to import the data file
<column Source= "1" (this is very important, specify the above source data field ID, when the source data and Target data field order or number of inconsistent, we can modify here to achieve the results we want) name= "Col1" (Target Field name) xsi:type= " Sqlsmallint "(Target field type)/>
</row>
*/
--below we create the source data file, paste the following into our specified data file C:\myTestFormatFiles-c.txt
/*
10,field2,field3,field4
15,field2,field3,field4
46,field2,field3,field4
58,field2,field3, FIELD4
*/
-import data using bcp format file
exec master. xp_cmdshell ' bcp Db_oa. myTestFormatFiles in C:\myTestFormatFiles-c.txt-f C:\myTestFormatFiles.Xml-T '
--View Imported data
Select * FROM myTestFormatFiles
/*
Col1 Col2 Col3 Col4
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
*/

--Import data using BULK INSERT
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
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
*/

--Import with OPENROWSET bulk rowset 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
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
*/

--Delete test table
DROP TABLE myTestFormatFiles

SQL Server bcp uses small closing