Bulk inserts into SQL Server 2000 from Flat Files

[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.

 

[David Browne]

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,

Yes it is.

and results in records getting dropped.

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.

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

 

[Jeff Jarrell]

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).

 

[Kevin Yu [MSFT]]

Hi Jason,

I agree with David, that you might have a bug in you app when records get
dropped.

You can set up a typed DataSet, a SqlDataAdapter and use
SqlDataAdapter.Update to put all the data into database.

Here is an example

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconupdatingdatabasewithdataadapterdataset.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Paul Clement]

¤ 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)