moving data from one db to another

[Keith G Hicks]

I have a pretty general question about what might be the best way to handle
this. I need to write a watcher program to export flagged rows in an MSSQL
2005 db out to a MySQL db. After each row is copied, I need to reset the
flag in the source. I'm wondering if anyone could tell me what the best
process for this would be. Looping through all the rows in the source one at
a time and then posting them one at a time seems like the wrong way to go.
There's a lot of vb.net that I don't know so I'm thinking there might be a
better, simpler way to do this. Initially there will be a couple hundred
thousand rows to copy. After that, there will be several hundred per day.

TIA,

Keith

 

[Keith G Hicks]

That parts all set. I've written several watchers before. But nothing that
copies data from one database to another. That's the part I'm trying to
figure out how best to do. Like I said, I have a few ideas but row by row
seems clunky. I'm lookinig for advice on the code to write that will do the
copying.

 

[Keith G Hicks]

FYI. I'm not lookign for anyone to write my code for me. I'm asking for help
in determining what the best strategy is in vb.net. So far I already wrote
code that opens up the source table (1000 rows at a time) in a data reader.
I loop through each row of those 1000, copy the column values into varialbes
and then insert them one row at a time into the target. It takes about one
minute to do 1000 rows. Seems pretty fast but I'm guessing there's a faster
way. Got about 1/2 million rows to copy over and 8 hours or so seems like a
very long time. I've dug into the help and cannot find anything better. But
as many of you know the vb.net help system is as daunting as vb.net itself.
I sure miss chm help.

 

[Jason Keats]

FYI. I'm not lookign for anyone to write my code for me. I'm asking for help
in determining what the best strategy is in vb.net. So far I already wrote
code that opens up the source table (1000 rows at a time) in a data reader.
I loop through each row of those 1000, copy the column values into varialbes
and then insert them one row at a time into the target. It takes about one
minute to do 1000 rows. Seems pretty fast but I'm guessing there's a faster
way. Got about 1/2 million rows to copy over and 8 hours or so seems like a
very long time. I've dug into the help and cannot find anything better. But
as many of you know the vb.net help system is as daunting as vb.net itself.
I sure miss chm help.

Remove indexes on the table you're inserting to, do the inserts, then
recreate the indexes.

If executing "INSERT INTO..." type strings then concatenate several
hundred - don't execute one row at a time.

Alternatively, create a datatable containing (say) a thousand rows, then
save that. Repeat until done.

HTH

 

[Keith G Hicks]

Yes. Thank you.

Remove indexes on the table you're inserting to, do the inserts, then
recreate the indexes.

If executing "INSERT INTO..." type strings then concatenate several
hundred - don't execute one row at a time.

Alternatively, create a datatable containing (say) a thousand rows, then
save that. Repeat until done.

HTH