writing while reading RRS feed

  • Question

  • Hello,
    Im wondering what is the best sollution for problem described below:
    I have table with sth like 30*10^6 records. Now i want to read each record and update or insert new record to another table. I need to aggregate some data.
    while (myReader.Read()){...}
    Is perfect cause I can get values of each record but I cant in this loop do inserts cause connection (to database) is busy.
    Thanks for any hints
    Sunday, May 24, 2009 5:51 PM

All replies

  • As of Microsoft SQL Server 2005 and up, there is the Multiple Active Result Sets (MARS) feature: http://msdn.microsoft.com/en-us/library/cfa084cz.aspx

    Alternatively, hold the list of updates in memory until all of the reads are completed.  Then loop through and perform all of your updates.
    Monday, May 25, 2009 2:27 PM
  • If you need to update and insert new rows to another table then create another database connection.

    I'm not sure what you're doing after the Read but it sounds like you're attempting to use the Connection object that is associated with the DataReader for your updates.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, May 26, 2009 4:07 PM