What is the fastest way to get 1,000,000 lines of fixed width text into a SQL Server database?

Question

I have a file with about 1,000,000 lines of fixed width data in it.

I can read it, parse it, do all that.

What I don't know is the best way to put it into a SQL Server database programmatically. I need to do it either via T-SQL or Delphi or C# (in other words, a command line solution isn't what I need...)

I know about BULK INSERT, but that appears to work with CSV only.....?

Should I create a CSV file from my fixed width data and BULK INSERT that?

By "fastest" I mean "Least amount of processing time in SQL Server".

My desire is to automate this so that it is easy for a "clerk" to select the input file and push a button to make it happen.

What's the best way to get the huge number of fixed width records into a SQL Server table?

Answer 1

I assume that by "fastest" you mean run-time:

The fastest way to do this from compiled code is to use the SQLBulkCopy methods to insert the data directly into your target table. You will have to write your own code to open and read the source file and then split it into the appropriate columns according to their fixed-width offsets and then feed that to SQLBulkCopy. (I think that I have an example of this somewhere, if you want to go this route)

The fastest way to do this from T-SQL would be to shell out to DOS and then use BCP to load the file directly into your target table. You will need to make a BCP Format File that defines the fixed-width columns for this appraoch.

The fastest way to do this from T-SQL, without using any CLI, is to use BULK INSERT to load the file into a staging table with only one column as DATA VARCHAR(MAX) (make that NVARCHAR(MAX) if the file has unicode data in it). Then execute a SQL query you write to split the DATA column into its fixed-width fields and then insert them into your target file. This should only take a single INSERT statement, though it could be a big one. (I have an example of this somewhere as well)

Your other 'fastest' option would be to use an SSIS package or the SQL Server Import Wizard (they're actually the same thing, under the hood). SSIS has a pretty steep learning curve, so it's only really worth it if you expect to be doing this (or things like this) for other cases in the future as well.

On the other hand, the Wizard is fairly easy to use as a one-off. The Wizard can also make a schedulable job, so if you need to repeat the same thing every night, that's certainly the easiest, as long as it actually works on your case/file/data. If it doesn't then it can be a real headache to get it right, but fixed-width data should not be a problem.

The fastest of all of these options has always been (and likely will always be) BCP.