use c# to copy table data from oracle to SQL Server?

Question

Could you kindly tell me whether there is a most convenient way to copy table data from oracle to SQL Server?

My only idea is iterate all rows and do insert operation. That means i should write lots of code.

I want to know may I use DataSet/DataAdapter or other convenient C# methods to do migration?

PS. under C#/.NET2.0 env.

Thanks indeed.

Answer 1

This is really going to depend on how much data you are working with and what you want to accomplish.

If there isn't a lot of data, you can do things the really quick and dirty way and read the entire set into memory via a DataSet, then simply insert record by record into SQL Server. That will work as long as you don't have a massive amount of data to move.

Now, if you have more data, you can go a bit more streamlined to avoid the "in memory" hit and do things using a DataReaders and only read in line by line, a bit more code, but not all that much, maybe 20-30 lines.

Now, depending on your needs, SQL Server Edition, etc you could also use SSIS to pull it in and not write any code. This is a post that shows some about the performance of this process.