Incrementally reading in table data

  • Thread starter Thread starter borophyll
  • Start date Start date
B

borophyll

Hi, I am developing an application which accesses an SQL server
database, in which certain tables contain millions of records.
Periodically we receive updates to the database from another
organisation (in the form of Access databases) and need to process
through every record in the Access database, compare the current
record to the existing record, and update with any changes found.

Because there are millions of records, it would be unwise to read the
Access table into a DataSet, as this would require too much memory.
Rather, I am guessing that I need to read a portion of the table in at
a time (say 1000 records), process through them, and then read the
next 1000 records, and so on. Is this possible? If it is I'm not
sure how to do it. I guess I need some sort of a SELECT command which
remembers the position of the last record selected, and continues from
that. I am new at this stuff, so any help as to which avenue I should
be heading down would be great.

Thanks,
B.
 
Hi,

Check out OleDbDataReader guy for reading sequentially from Access.
Click to expand...

Thanks, that looks like what I need.

So, I guess I would iterate over the records of the Access database at
the same time as I iterate over the records of the SQL server
database. If an Access record doesn't exist, insert it, if they are
different, update it. If there is an SQL server record present thats
not in the access database, it must be old so delete it. The two
database tables share a common primary key, so I guess I could do an
"order by" on PK and compare as I iterate.

Is it OK for me to insert, update and delete records from the SQL
table while I have a SqlDataReader open? Or do I somehow have to keep
a record of these changes and do this after the DataReader is closed?
 
You can do it in one method with both connections open.

Be aware that a Jet database (by you called Access) is a databasefile, not a
database server.

Cor

Hi,

Check out OleDbDataReader guy for reading sequentially from Access.
Click to expand...

Thanks, that looks like what I need.

So, I guess I would iterate over the records of the Access database at
the same time as I iterate over the records of the SQL server
database. If an Access record doesn't exist, insert it, if they are
different, update it. If there is an SQL server record present thats
not in the access database, it must be old so delete it. The two
database tables share a common primary key, so I guess I could do an
"order by" on PK and compare as I iterate.

Is it OK for me to insert, update and delete records from the SQL
table while I have a SqlDataReader open? Or do I somehow have to keep
a record of these changes and do this after the DataReader is closed?
 
Yep, as Cor replied it is perfectly safe to do operations on *different*
connection instances at the same time.
 
Back
Top