|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Bulk inserts into SQL Server 2000 from Flat Filesmy 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" <Jasonthemasonkcch@newsgroups.nospam> wrote in message Yes it is.news:eiSuoVzNGHA.3936@TK2MSFTNGP12.phx.gbl... > 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. No it doesn't, you had a bug.>I've looked at creating a dataset, but haven't found any examples online Either upgrade to ADO.NET 2.0 / SQL Server 2005 and use SQLBulkCopy>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. > 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). Show quote "Jason Allred" <Jasonthemasonkcch@newsgroups.nospam> wrote in message news:eiSuoVzNGHA.3936@TK2MSFTNGP12.phx.gbl... > 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. > 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." On Tue, 21 Feb 2006 15:23:12 -0700, "Jason Allred" <Jasonthemasonkcch@newsgroups.nospam> wrote: ¤ 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)  |
|||||||||||||||||||||||
Other interesting topics