Bulk inserts into SQL Server 2000 from Flat Files

  • Thread starter Thread starter Jason Allred
  • Start date Start date
J

Jason Allred

This is my first post, so if this has been addressed before, please excuse
my ignorance...

I'm writing a Window's Service in VB.NET to take information from flat files
that contain GPS information and subsequently save them to a SQL Server
(2000) database. I have created classes / collections that do all the work
of retrieving the information from the flat files. That seems to have been
the easy part. Now in trying to get them into the DB, (just one table), I
find that creating several thousand insert statements is very unwieldy, and
results in records getting dropped. I've looked at creating a dataset, but
haven't found any examples online that I could twist to my purposes yet.

The main problem I seem to be having is first getting the information from
the objects into a dataset, and then subsequently into the database. Does
anyone have any advice.

Regards,

Jason.
 
Jason Allred said:
This is my first post, so if this has been addressed before, please excuse
my ignorance...

I'm writing a Window's Service in VB.NET to take information from flat
files that contain GPS information and subsequently save them to a SQL
Server (2000) database. I have created classes / collections that do all
the work of retrieving the information from the flat files. That seems to
have been the easy part. Now in trying to get them into the DB, (just one
table), I find that creating several thousand insert statements is very
unwieldy,
Click to expand...

Yes it is.
and results in records getting dropped.
Click to expand...

No it doesn't, you had a bug.
I've looked at creating a dataset, but haven't found any examples online
that I could twist to my purposes yet.

The main problem I seem to be having is first getting the information from
the objects into a dataset, and then subsequently into the database. Does
anyone have any advice.
Click to expand...

Either upgrade to ADO.NET 2.0 / SQL Server 2005 and use SQLBulkCopy
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Or just use SqlCommand and insert statements. Remember to wrap the
individual inserts in a SqlTransaction for the best performance.

David
 
BulkCopy is the way to go. While you may not be able to do it the "best
way" as David's Brown's post suggests because of your product versions. I
haven't kept up but what I have done this in the past, write a preprocessor
to convert the incoming flat file to another flat file (and create the
bcp.fmt file) that is more hospitable to bcp. Then shell out and run
bcp.exe. It sounds like a lot with the extra transform but it won't hurt
you like inserting one record at time into SQL will.

(bcp.exe is a command line utility that comes with the mssql tools).
 
¤ This is my first post, so if this has been addressed before, please excuse
¤ my ignorance...
¤
¤ I'm writing a Window's Service in VB.NET to take information from flat files
¤ that contain GPS information and subsequently save them to a SQL Server
¤ (2000) database. I have created classes / collections that do all the work
¤ of retrieving the information from the flat files. That seems to have been
¤ the easy part. Now in trying to get them into the DB, (just one table), I
¤ find that creating several thousand insert statements is very unwieldy, and
¤ results in records getting dropped. I've looked at creating a dataset, but
¤ haven't found any examples online that I could twist to my purposes yet.
¤
¤ The main problem I seem to be having is first getting the information from
¤ the objects into a dataset, and then subsequently into the database. Does
¤ anyone have any advice.

If you're still on .NET 2003 use BULK INSERT instead:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top