CODEWITHSUNDEEP
sqlsql-serverinsertmerge
crackruckles
crackruckles

Reputation: 386

Fastest way to add data to database

Hi guys i have 5 rather large excel files (800,000 lines each) that i am importing into a sql database. I have written a tool to do this but i am importing one line at a time from one spreadsheet after another which is taking about 15 hours to complete and since this needs to be done once a month ide like to know whats the fastest way of doing this.

Would it be faster running all five imports at once but to different temporary table and merging them, or is it just faster inserting and updating and does bulk insert increase the speed at all?

Many Thanks

Upvotes: 0

Views: 2128

Answers (3)

Dan Guzman
Dan Guzman

Reputation: 46231

A bulk insert technique will be fastest. It is common to achieve a rate of millions of records per minute but much depends on the number of columns, row size, hardware. etc.

The command-line BCP utility and T-SQL BULK INSERT statement do not support Excel files natively but can handle delimited text files. You might consider using an SQL Server Integration Services package, which can import Excel files directly and using a bulk insert method when the fast load option is specified. There is a bit of learning curve if you haven't used SSIS before. Another alternative is using SqlBulkCopy from a .NET application.

SQL Server bulk insert stream data so there is no hard limit on the number of records per batch. For very large files, it is common to perform the bulk insert in multiple batches in order to reduce transaction log space requirements when the operation is fully-logged.

Upvotes: 2

Mahernoz
Mahernoz

Reputation: 79

There is the bcp command in sql server. Which opens up a utility exactly for this purpose.

Import and Export Bulk Data by Using the bcp Utility (SQL Server)

Upvotes: 0

SouravA
SouravA

Reputation: 5253

Try using the BCP command. It's generally faster than regular inserts.

bcp {dbtable | query} {in | out | queryout | format} datafile

[-n native type] [-c character type] [-S server name] [-U username]

[-P password] [-T trusted connection]

Also check if the table to which you are importing has indexes. Try dropping the indexes and inserting the data.

Upvotes: 1

Related Questions