What Are The Pitfalls Of Inserting Millions Of Records Into Sql Server From Flat File?

I am about to start on a journey writing a windows forms application that will open a txt file that is pipe delimited and about 230 mb in size. This app will then insert this data

Solution 1:

Do you have to write a winforms app? It might be much easier and faster to use SSIS. There are some built-in tasks available especially Bulk Insert task.

Also, worth checking Flat File Bulk Import methods speed comparison in SQL Server 2005.

Update: If you are new to SSIS, check out some of these sites to get you on fast track. 1) SSIS Control Flow Basics 2) Getting Started with SQL Server Integration Services

This is another How to: on importing Excel file into SQL 2005.

Solution 2:

This is going to be a streaming endeavor.

If you can, do not use transactions here. The transactional cost will simply be too great.

So what you're going to do is read the file a line at a time and insert it in a line at a time. You should dump failed inserts into another file that you can diagnose later and see where they failed.

At first I would go ahead and try a bulk insert of a couple of hundred rows just to see that the streaming is working properly and then you can open up all you want.

Solution 3:

You could try using SqlBulkCopy. It lets you pull from "any data source".

Solution 4:

Just as a side note, it's sometimes faster to drop the indices of your table and recreate them after the bulk insert operation.

Solution 5:

You might consider switching from full recovery to bulk-logged. This will help to keep your backups a reasonable size.