Re: Bulk inserts into SQL Server 2000 from Flat Files

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"Jason Allred" <Jasonthemasonkcch@xxxxxxxxxxxxxxxxx> wrote in message
news:eiSuoVzNGHA.3936@xxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Abstracting SQL Statements In An Object
    ... you're interface does not abstract the sql at all. ... A proper abstraction layer removes all dependencies from the database. ... When they get the database problem resolved, it will be a simple matter to drop MySQL based classes in place of the flat file. ...
    (comp.lang.php)
  • Error handling with performance improvement
    ... I'm using SQL adapter to insert ... records into the database. ... I read these records from a flat file. ... are caught using the DeliveryNotificationException. ...
    (microsoft.public.biztalk.general)
  • Re: Need Help deleting record from text file
    ... In many circumstances retrieval of data from a "home grown" data file will actually be faster than from a general purpose commercial database. ... save his data to disk using a "flat file" unindexed arrangement. ... In fact I never mentioned him saving his data to disk in any format at all. ... Admittedly a nice "ready made" database does make it relatively easy for people to store and retrieve data fairly rapidly without needing to write too much code or to worry about the methods used by the "black box" to perform such feats, and I certainly would not suggest that they should not be used, but I think that for many tasks a "home grown" data format would be as good as, and in some cases better than, a standard database. ...
    (microsoft.public.vb.general.discussion)
  • Re: Having difficulty refactoring a DB application
    ... "Flat file" is not the same as "file". ... But don't you have a need for putting the data from the database into ... update SQL immediately when you want to update a column value. ... > terribly useful in most cases to think of objects being mapped into tables, ...
    (comp.object)
  • Re: Just say no to threads [Was: Software architecture]
    ... they knew there was going to be a database in the app. ... Now my colleague just spent a couple/three weeks designing an SQL ... Turns out they have a flat file of sample information, ...
    (comp.object)