Use the BCP command to input a large amount of data

Due to the large data volume imported some time ago Program . Problems encountered during the process. tens of thousands of pieces of data are imported into the database from Excel, which is very slow and the program cannot be run. Later I studied the method of BCP command for importing. tens of thousands of pieces of data are tens of seconds, so I will share my small achievements with you. of course, there may be a lot to improve. Let's work together.

UseBCPCommand input a large amount of data

 

1:Purpose:Input Big DataSQL ServerAnd other databases

2:Method: BCPCommand

3:Implementation Method:

SQLStatement:

BulkInsertT1From 'C: "txtfile2.txt'

With

(Fieldterminator=',',

Rows_per_batch=100,

Errorfile='C: "aa.csv',

Formatfile='C: "SQL. fmt'

)

from TXT or CSV Format , two formats fieldterminator ','.

In addition, for a table,I can import only some fields.This is what we must useFormatfileFormat File ing.The file format is"FMT"FormatSyntax:

<? 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 = "50" collation = "SQL _latin1_general_cp1_ci_as"/>

<Field ID = "2" xsi: TYPE = "charterm" Terminator = "," max_length = "200" collation = "SQL _latin1_general_cp1_ci_as"/>

<Field ID = "3" xsi: TYPE = "charterm" Terminator = "R" N "max_length =" 50 "/>

</Record>

<Row>

<Column source = "1" name = "ID" xsi: TYPE = "sqlvarychar"/>

<Column source = "2" name = "firstname" xsi: TYPE = "sqlvarychar"/>

<Column source = "3" name = "Age" xsi: TYPE = "sqlint"/>

</Row>

</Bcpformat>

Note:TerminatorSettings.Yes","Or"" T0000.txtAndCSVThe file is","

ExcelFormat file:"" T ".

CurrentlyTxtAndCSVFormat Import,ForXlsFile Import,In further research