I have a table with atleast 12 million rows of data in a table that looks like this:
Id (varchar(10) | Image (varbinary(max)
---------------- -------------------------
X123456789 | 0x....
1121132121 | 0x....
JF13232131 | 0x....
What would be an efficient way to move data from this table to another table in chunks. I'm writing a simple .net console app to do this and i fear that when i try to load all 12 million rows at once my app will crash.
Thanks for any help
Addendum #1: The source db is oracle and the destination db is sql server
Addendum #2: I'm also converting the image data from tiff to jpeg
"What would be an efficient way to move data from this table to another table in chunks"
You think you need to move the data in chunks because you're hand-rolling a tool instead of using SSIS. You think you can't use SSIS because you want to do "some converting/translating on the data after the initial pull". What transformations do you want to apply which you think you can't do with SSIS? I mean SSIS can do loads of things. Find out more.
Because using a purpose-built tool is way more efficient than trying to write your own. And the other thing is, migrating the whole table in a single operation is a much safer bet with regards to avoiding data corruption and data loss.
"I'm converting the images from tiffs to jpegs. Can SSIS do this for me?"
Okay, so clearly that is a rather specialized requirement and not the sort of thing we would expect SSIS to have as a built-in. But SSIS does support custom transformations so you could write a component which converts the images.