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 containing about 1,000,000 rows of data of a fixed width.

I can read it, make it out, do it all.

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

I know about BULK INSERT , but it seems to work only with CSV .....?

Should I create a CSV file from data of fixed width and BULK INSERT , what?

By "fastest," I mean "Least Processing Time in SQL Server."

My desire is to automate this, so it’s easy for a “clerk” to select an input file and press a button to make it happen.

What is the best way to get a huge number of fixed-width records in 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 SQLBulkCopy methods to insert data directly into your target table. You will need 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 pass them to SQLBulkCopy. (I think I have an example of this somewhere if you want to go this route)

The fastest way to do this from T-SQL is to upload it to DOS and then use BCP to upload the file directly to your target table. You will need to create a BCP format file that defines fixed-width columns for this estimate.

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

Another “fastest” option would be to use the SSIS package or the SQL Server Import Wizard (they are actually the same thing under the hood). SSIS has a pretty steep learning curve, so it's really worth it if you expect to do this (or such things) for other occasions in the future.

Wizard, on the other hand, is pretty easy to use as a one-time use. A wizard can also carry out a scheduled task, so if you need to repeat the same thing every night, this is by far the easiest if it really works on your case / file / data. If this is not the case, then it can be a real headache to get it right, but fixed-width data should not be a problem.

The fastest of all these parameters has always been (and probably always will be) BCP.

Answer 2

I personally would do this using the SSIS package. It has the flexibility to handle fixed widths.

If this is a one-time download, use the wizard to import data. If not. create the package yourself, and then plan it to run periodically.

Answer 3

What I am doing is loading the IDataReader, which is connected to the import file.

Then I loop over the IDataReader, check each row, sometimes massage the data in each row, and then click on it in Xml (or DataSet and copy the ds.GetXml () method).

Then each one has so many lines (say, every thousand), I click them on a stored procedure that can handle xml input.

If one line does not pass the test, I will write it down later. (If I had 1,000,000 lines and it was ok to skip one, so I correctly imported 999,999 lines, I later process the error record).

If my massive xml insert fails (with 1000 lines in it), I am registering all xml. You can go through an unsuccessful set (out of 1000) and import these 1 by 1, and write the bad ones, as I assume. Aka, do 1000 at a time until 1000 works, then do them 1 on 1.

I have an example written here:

http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

Answer 4

You have a choice, but it depends on what you mean by the fastest. Fastest for one completion from which I will do it now? There is a wizard in SQL Management Studio. The fastest way to do this is monthly with a minimal learning curve. SQL Managment Studio has a DTS wizard. Minimum SQL engine cycles for doing this every night? SSIS http://en.wikipedia.org/wiki/SQL_Server_Integration_Services

Answer 5

bulk insert or bcp is the fastest way to do this because it is an incomplete operation. From my experience, you can easily insert 10k rows per second.

To insert fixed-width bulk data, you need to create a bulk copy format file:

http://msdn.microsoft.com/en-us/library/ms178129.aspx