Use bulk insert to efficiently import large amounts of data to the SQL Server database

Source data (text files)

A large amount of historical stock data has been downloaded in the text format:

The first line of each file contains shares.Code, Stock name, data type. The second row is the name of the data column:

Data Table

A new data table teststock is created in the database and the following fields are set, but there is no "transaction amount" field, because this data will not be used in future computation. in addition, the price field does not use the money data type. decimal is enough.

Write formatted files

For details about how to compile formatted files, see:

1. Architecture Syntax of XML formatted files

2. XML Format File example

The format file of the current data is:

 <?  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  = " , " /> <  Field  ID  = " 2 " Xsi: Type  = " Charterm " Terminator  = " , " /> <  Field  ID  = " 3 " Xsi: Type  = " Charterm " Terminator  = " , " /> < Field  ID  = " 4 " Xsi: Type  = " Charterm " Terminator  = " , " /> <  Field  ID  = " 5 " Xsi: Type = " Charterm " Terminator  = " , " /> <  Field  ID  = " 6 " Xsi: Type  = " Charterm " Terminator  = " , " /> <  Field  ID  = " 7 " Xsi: Type  = " Charterm " Terminator  = " \ R \ n " /> </  Record  > <  Row  > < Column  Source  = " 1 " Name  = " Date " Xsi: Type  = " Sqldate " /> <  Column  Source  = " 2 " Name = " Openprice " Xsi: Type  = " Sqldecimal " Precision  = " 6 " Scale  = " 2 " /> <  Column  Source  = " 3 " Name  = " Highprice " Xsi: Type  = " Sqldecimal " Precision  = " 6 " Scale  = " 2 " /> <  Column  Source  = " 4 " Name  = " Lowprice " Xsi: Type  = " Sqldecimal " Precision  = " 6 " Scale  = " 2 " /> <  Column Source  = " 5 " Name  = " Closeprice " Xsi: Type  = " Sqldecimal " Precision  = " 6 " Scale  = " 2 "/> <  Column  Source  = " 6 " Name  = " Volumn " Xsi: Type  = " Sqlint " /> </  Row  > </  Bcpformat  > 

For the moment, store the C directory. The file name is bcpformat. xml.

Compiling bulk insert statements

For syntax of bulk insert, refer to this document. The parameters used here are mainly formatfile, fieldterminator, and rowterminator.

Bulk insert teststock

From 'C: \ sh600475.txt'

With (

Formatfile = 'C: \ bcpformat. xml ',

Fieldterminator = ',',

Rowterminator = '\ r \ n ')

We should also use the firstrow attribute because I want to skip the first two lines of text. After all, the third row is the real data. however, I encountered the same problem as this post, that is, when firstrow is set to 3, it actually starts to input data from 5th lines of text, when I set firstrow to 1 (this time it was supposed to be input from line 3 of the text), an error is returned, indicating that the data format of a row is incorrect. Then I processed the data: before executing the bulk insert statement, delete the first two lines of the text, and do not specify the firstrow attribute in the bulk insert statement. I hope someone can tell me what to do here...

Run

Bulk insert is very fast. In this example, it takes almost no time and achieves the desired effect:

Similarly, if you read a record from the text and execute an insert into statement, it takes about 10 seconds. This shows that bulk insert is efficient.

Reference

1. http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

2. http://msdn.microsoft.com/zh-cn/library/ms189327.aspx

3. http://msdn.microsoft.com/zh-cn/library/ms191234.aspx

4. http://stackoverflow.com/questions/1029384/sql-bulk-insert-with-firstrow-parameter-skips-the-following-line

Link: http://www.cnblogs.com/technology/archive/2011/08/10/2133734.html