Bulk Insert - Fixed Width File

[sbushway]

Hi,
I have a program that reads about 7000 records from a flatfile and inserts them into a SQL Server 2000 table. This takes about 2.5 minutes (using VB.NET). I was told that a Bulk Insert would be much faster.

All the examples I've seen have a FIELDTERMINATOR - I don't have any FIELDTERMINATORs in my file. It's a fixed-width flatfile.

example:

JOE JONES      ATLANTA      GA
ROBERT SMITH   TALLAHASSEE  FL
NATALIE WILLIAMMIAMI        FL

Can you do a bulk insert with a fixed-width file?

Any help would be appreciated!

Thanks in advance,
Suzanne

 

[sbushway]

One more thing:
Or should I use a Bulk Copy Program?

I'm confused as to which would be the best method for my situation: reading a text file that contains about 7000 fixed-length records and then inserting those records into a table.

Thanks again,
Suzanne

 

[esdee]

read article "bulk copy, copying data from data file to SQL Server" in books online

generally using bcp or bulk insert is faster than loading data from a higher level programming language(VB.NET in your case)

i dont think theres a problem to do a bulk insert with a fixed-width file; with bcp you can use a format_file option to specify the format of the imported file

you could also use a dts task to bulk insert your data,
just create a connection, drop a bulk insert task from the menu, set the properties and thats it...but thats of course if you need to schedule the task

 

[chebbi]

sbushway..Did you find the solution to your problem. Iam trying to do the exact same thing. If you have found the solution and if you can share with me your text and format file then that would be great.

Badrinath Chebbi

http://www.bmchebbi.com

 

[nigelrivett]

You can indeed.
You may want to use a format file to define the size of the fields
see

http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

That is for csv files but fixed width are similar.
You also may want to have a look at

http://www.nigelrivett.net/ImportTextFiles.html

It is often easier to import into a single column staging table then process from there.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.