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 开发者_Go百科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.

You should have a look at the SqlBulkCopy class, you may be able use it to move the entire table in one shot.

The way I'd tackle the problem would be to connect to the Oracle database in .Net and use a DataReader to read the data out row by row. Then process each row to do your image conversion and then simply insert the new data into your Sql Server database. Now, I'd imagine that this would not be a quick thing to do, but I can't see any reason why it would crash as the data is streamed from Oracle and then pushed straight into Sql Server.

If you want to make it run a bit quicker then it wouldn't be too hard to have multiple threads doing the transformation and insert into Sql Server.

Have you looked at BCP? We had a similar problem and it worked fine for us.