I have an Oracle database in which there is one or more table. I also have blob data, i.e. images are stored, now I would like to move that data to Sql Server database. What is the best way to do this?
I would like to test it for one table migration which contains image data in the Oracle database and move it into SQL Server table.
How do I test for migrating data from one table from Oracle to SQL Server?
But first thing I would like to confirm is that whether Image data moving from Oracle DB to SQL Server DB is possible, is there anything that has to be taken care of while migrating ?
I'd recommend having a look at SQL Server Integration Services (SSIS).
SSIS is a ETL (Extract, Transform and Load) tool built for Data Warehousing, but it's also great for migrating data. It basically replaces the old SQL Server Data Transformation Services (DTS), and is a robust and fast-performing tool allowing you to move data from one system to another (which includes Oracle), while giving you the ability to transform data where necessary.
It also has work-flow capabilities, allowing you to set up an execution path (i.e. load client data prior to loading invoice data).
While building up the individual steps, you can easily have a look at the data coming in and flowing through the different objects you set up.
Best of all is that if you run at least SQL Server Standard Edition, SSIS basically comes with it for free.
Lastly, regarding the question about images, blobs are just blobs. Streams of binary data. I haven't tried migrating images from one to the other, but I see no reason why it would not work.
