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

Source: Internet
Author: User
Tags bulk insert

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:

  1. <? xmlversion xmlversion = "1.0" ?>
  2. < Bcpformat
  3. xmlns = "Http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
  4. Xmlns:xsi = "Http://www.w3.org/2001/XMLSchema-instance" >
  5. < RECORD >
  6. < FieldID FieldID = "1" XSI : Type = "Charterm" TERMINATOR ="," />
  7. < FieldID FieldID = "2" XSI : Type = "Charterm" TERMINATOR ="," />
  8. < FieldID FieldID = "3" XSI : Type = "Charterm" TERMINATOR ="," />
  9. < FieldID FieldID = "4" XSI : Type = "Charterm" TERMINATOR ="," />
  10. < FieldID FieldID = "5" XSI : Type = "Charterm" TERMINATOR ="," />
  11. < FieldID FieldID = "6" XSI : Type = "Charterm" TERMINATOR ="," />
  12. < FieldID FieldID = "7" XSI : Type = "Charterm" TERMINATOR = "\ r \ n" />
  13. </ RECORD >
  14. < ROW >
  15. < Columnsource Columnsource = "1" NAME = "Date" Xsi:type = "Sqldate" />
  16. < Columnsource Columnsource = "2" NAME = "Openprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
  17. < Columnsource Columnsource = "3" NAME = "Highprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
  18. < Columnsource Columnsource = "4" NAME = "Lowprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
  19. < Columnsource Columnsource = "5" NAME = "Closeprice" Xsi:type = "SqlDecimal" PRECISION = "6" Scale = "2" />
  20. < Columnsource Columnsource = "6" NAME = "Volumn" Xsi:type = "Sqlint" />
  21. </ ROW >
  22. </ 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.