BULK Insert how to efficiently import large amounts of data to SQL Server

Reprinted from: Http://database.51cto.com/art/201108/282631.htmBULK insert how to efficiently import large amounts of data into SQL Server

In this article we describe in detail how bulk insert can efficiently import large amounts of data into a SQL Server database, and hopefully this introduction will help you.

AD:
WOT2014 Course Recommendation: Actual combat MSA: using open source software to build micro-service system

In the actual work needs, we sometimes need to import a large amount of data into the database. What we have to consider at this point is efficiency. In this article, we describe a way to efficiently import large amounts of data into a SQL Server database using BULK INSERT , and then let's take a look at this part.

source data (text file)

Download a large number of stock history data, all in text format:

The first line of each file contains the stock code, the stock name, and the data type. The second row is the name of the data column:

Data Sheet

A new data table Teststock is created in the database, and the following fields are set, but there is no field for "turnover" because this data is not used in future calculations. In addition the Money data type is not used in the field about price, decimal is sufficient.

Writing a format file

The format file for the current data is:

2. <? xmlversion xmlversion = "1.0" ?>
4. < Bcpformat
6. xmlns = "Http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
8. Xmlns:xsi = "Http://www.w3.org/2001/XMLSchema-instance" >
10. < RECORD >
12. < FieldID FieldID = "1" XSI : Type = "Charterm" TERMINATOR ="," />
14. < FieldID FieldID = "2" XSI : Type = "Charterm" TERMINATOR ="," />
16. < FieldID FieldID = "3" XSI : Type = "Charterm" TERMINATOR ="," />
18. < FieldID FieldID = "4" XSI : Type = "Charterm" TERMINATOR ="," />
20. < FieldID FieldID = "5" XSI : Type = "Charterm" TERMINATOR ="," />
22. < FieldID FieldID = "6" XSI : Type = "Charterm" TERMINATOR ="," />
24. < FieldID FieldID = "7" XSI : Type = "Charterm" TERMINATOR = "\ r \ n" />
26. </ RECORD >
28. < ROW >
30. < Columnsource Columnsource = "1" NAME = "Date" Xsi:type = "Sqldate" />
32. < Columnsource Columnsource = "2" NAME = "Openprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
34. < Columnsource Columnsource = "3" NAME = "Highprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
36. < Columnsource Columnsource = "4" NAME = "Lowprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
38. < Columnsource Columnsource = "5" NAME = "Closeprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
40. < Columnsource Columnsource = "6" NAME = "Volumn" Xsi:type = "Sqlint" />
42. </ ROW >
44. </ Bcpformat >

Save it in the C-disk directory, the file is named Bcpformat.xml.

Write the Bulkinsert statement:

1. Bulkinsertteststock
2. From ' C:\SH600475.txt '
3. With (
4. FormatFile = ' C:\BCPFORMAT.xml ' ,
5. FieldTerminator = ', ' ,
6. Rowterminator = ' \ r \ n ' )

The execution of the Bulkinsert is fast and the results are as follows:

The same effect, if you read a row of records from the text, the execution of a insertinto statement, it takes about 10 seconds, this shows bulkinsert efficient.

The knowledge about importing large amounts of data into a SQL Server database using BULK INSERT is here, and I hope this introduction will help you.