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.
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.