Re: Bulk inserts into SQL Server 2000 from Flat Files


"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,

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


.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)