The fastest way to import SQL Server massive data

Source: Internet
Author: User
Tags bulk insert
This forum article (CCID technical community) explains in detail the fastest way to import SQLServer massive data. For more information, see the following

This forum article (CCID technical community) explains in detail the fastest way to import SQL Server massive data. For more information, see the following

Recently, I analyzed the database of a project. to import a large amount of data, I want to import up to 2 million pieces of data to sqlserver at a time. If I use a normal insert statement to write the data, I am afraid that the task cannot be completed without an hour. bcp is considered first, but this is based on command line. It is too unfriendly for users and is unlikely to be used. Finally, we decided to use the BULK INSERT statement for implementation, bulk insert can also be used to import large amounts of data, and can be implemented through programming. The interface can be very friendly, and its speed is also high: importing 1 million pieces of data in less than 20 seconds, i'm afraid there is no right person in terms of speed.
However, using this method also has several disadvantages:
1. Tables whose data needs to be exclusively accepted
2. A large number of logs are generated.
3. There are restrictions on the format of the files from which data is retrieved
However, compared with its speed, these shortcomings can be overcome, and if you are willing to sacrifice a little speed, you can also perform more precise control, or even control the insert of each row.
For logs that occupy a large amount of space, we can dynamically change the Database Log mode before import to the large-capacity log record recovery mode, so that no logs will be recorded, after the import is complete, restore the original database logging method.
The specific statement can be written as follows:
The Code is as follows:
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 exports the data file from e: \ out.txt to the detail table of the database taxi.

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.