Moving large amounts of data from one database to another in chunks
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.
First of all, I wouldn't attempt to move 12M rows via a .NET app. I would use a tool like SSIS (SQL Server Integrated Services) to do this. SSIS is very fast, can support transactions and supports sophisticated data transformations. SSIS was designed to do this sort of thing.
However, if you must do this within a .NET application. I would probably read something like 1K to 5K rows at a time, insert them into the target database, and get the next 1K to 5K rows etc. But this is going to be much slower than via a tool like SSIS.