SQL Server mass Data import method

Source: Internet
Author: User
Keywords Network programming MSSQL tutorials
Tags based command line data high high speed import import method it is

To realize the problem of importing massive data, import millions of data into SQL Server one at a time, and if you write with a normal INSERT statement, I'm afraid it won't be completed in a few hours, first consider using bcp, but it's based on the command line, and it's too unfriendly for the user to actually use Finally decided to use the BULK INSERT statement implementation, BULK Insert can also achieve large amount of data import, and can be programmed to do, the interface can be done very friendly, it is very high speed: Import 1 million data in less than 20 seconds, this speed is indeed amazing.

But there are several drawbacks to using this approach:

1. Tables that require exclusive acceptance of data

2. Will generate a lot of log

3. The file from which data is extracted has formatting limitations

But these drawbacks can be overcome relative to its speed, and if you are willing to sacrifice a little bit of speed, you can do more precise control and even control the insertion of each row.

In the case of a large space-consuming log, we can take the log mode of dynamically changing the database before importing for the bulk-logged recovery model, so that the log is not logged and the original database logging is restored after the import is complete.

A specific statement that we can write:

ALTER DATABASE Taxi

Set RECOVERY bulk_logged

BULK INSERT Taxi ... Detail from ' E:out.txt '

With (

DataFileType = ' char ',

FieldTerminator = ', ',

Rowterminator = ' n ',

TABLOCK

)

ALTER DATABASE Taxi

Set RECOVERY full

This statement will export the data file from E:out.txt to the detail table in the database taxi.

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.