The fastest way to import massive data on SQL Server

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:
Copy codeThe 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.