Avatar of dodgerfan
dodgerfan
Flag for United States of America asked on

Import Oracle data into SQL Server 2012

I have a huge Oracle database that I need to import data from into specific tables in SQL server. Right now I have a web service that is slowly churning through the data. It's been running for 2 days now. I was hoping to find something more robust and quick to import the records in. I am talking literally over 100 million records. After 2 days I have 5 million imported. I am looking for any ideas on a better and much quicker solution. I'm using SQL Server 2012. The web service is written in C#, VS 2012. Any ideas would be appreciated.
Microsoft SQL ServerOracle DatabaseC#

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
dodgerfan

ASKER
Oh, I do have the web service threaded and am running 10 instances of it. Multyple open database connections, too.
Steve Wales

Have you tried SQL Server Migration Assistant ?

I've converted 200GB+ databases in less time that you've currently spent on what you're doing.

Both databases need to be visible from the server you're running SSMA on - and there are some limitations on complex functions etc.  But for pure data without any LOB's etc in it (may be a few other restrictions) it has served me well.

This is a great option for a one off thing, not so much if it's something you need to repeat regularly.
slightwv (䄆 Netminder)

Any reason you wrote your own?

I'm not a SQL Server person but I've read about tools that already do this for you.

http://technet.microsoft.com/en-us/library/ms141209(v=sql.110).aspx

I've also seen folks talking about SQL Server Integration Services (SSIS) that can do this.

I even think Oracle has a tool but theirs might be for getting data FROM SQL Server into Oracle.  Not sure about the other way.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Steve Wales

Link for SSMA (slightwv got in before I could edit my comment): http://technet.microsoft.com/en-us/library/hh313179.aspx

Guess it also depends if you're doing multiple tables or just one.  Import / Export may work just fine for one table.

SSMA was great when I had to convert a database of 1600 tables...
slightwv (䄆 Netminder)

>>After 2 days I have 5 million imported. I am looking for any ideas on a better and much quicker solution.

Assuming you wrote your own for a reason:
What are your commit points set to?
How is it doing the transactions on the Oracle side?

For example:
In Oracle a select is read consistent.  Once the cursor is opened, all data fetched will be as it was at the time it started. So, you may be going back and reading rows as they looked 2 days ago!  This would likely involve a lot of redo reading.

What I've done in the past when I had to write my own migration was create a temp table and load up all the primary key values that need migration.

My migrate program reads the values from that, moves the row then deletes the row from the temp table.

Then I can set up transactions however I want.  If the row is in the temp table, it still needs migrated.
johnsone

For pure simple one time transfers, I have created a linked server in SQL Server to Oracle and just done a simple insert into ... select from ...

That method worked and I would think it would be faster than what you are doing.

If this is an ongoing thing that method isn't as efficient, but it should work.

Another way would be to dump the data to a flat file (pretty easily done with TOAD or SQL*Plus) and then use BCP to load it in.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Alexandru Ungureanu

Give a test with Pentaho Data Integration (Kettle). They have a community edition here http://community.pentaho.com/ .
Maybe the tools mentioned above are more reliable, I don't know, but with Kettle I forget about data transfer between databases.

Of course you must take into consideration slightwv comment related to Oracle production data modification.

Regards Alex.
dodgerfan

ASKER
This is not a conversion. I'm pulling data out of a production Oracle db and putting it into a SQL server db that will serve as the backend to a COTS product. This initial import is to get everything I have in SQL, then set up a process to import new records on a daily basis. I have tried linked servers in the past, but I have been unsuccessful in getting oracle linked to this sql server thus far. The import code was provided by the COTS vendor. I tweaked it to allow for threading, but that's been it.
Alexandru Ungureanu

I definitively would go with Kettle, as I said. Initial load of course can be lengthy, but further based on an audit table on SQL Server side which keeps last ID loaded in previous day, can be used for reading from Oracle starting from that ID.
import.jpg
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.