SQL Server mass Data import method

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.