Shulou(Shulou.com)05/31 Report--

This article mainly explains "the method of importing a large amount of data into SQL Server". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the method of importing a large amount of data by SQL Server".

In order to import a large amount of data, import millions of pieces of data into sqlserver at a time. If you use ordinary insert statements to write out, you may not be able to complete it in a few hours. First, consider using bcp, but this is based on the command line, which is too friendly for users and is not likely to be used. Finally, it is decided to use the BULK INSERT statement to implement, BULK INSERT can also achieve a large amount of data import, and through the implementation, the interface can be very friendly, its speed is also very high: importing 1 million pieces of data in less than 20 seconds, the speed is really amazing.

But using this approach also has several disadvantages:

1. Tables that require exclusive acceptance of data

two。 Will generate a large number of logs

3. Files that fetch data from the file have format restrictions

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

In the case of generating logs that take up a lot of space, we can adopt the logging method of dynamic changes before import for the mass logging recovery model, so that the logs will not be logged, and then restore the original database logging mode after the import.

We can write a specific sentence like this:

Alter database taxi

Set RECOVERY BULK_LOGGED

BULK INSERT taxi..detail FROM e:out.txt

WITH (

?? DATAFILETYPE = char

?? FIELDTERMINATOR =

?? ROWTERMINATOR =

? TABLOCK

)

Alter database taxi

Set RECOVERY FULL

This statement exports the data file from e:out.txt to the detail table in the database taxi.

At this point, I believe you have a deeper understanding of the "SQL Server method of importing a large amount of data". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.