We help IT Professionals succeed at work.

Import Oracle data into SQL Server 2012

1,896 Views
Last Modified: 2014-05-29
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.
Comment
Watch Question

Author

Commented:
Oh, I do have the web service threaded and am running 10 instances of it. Multyple open database connections, too.
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
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...
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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.
Alexandru UngureanuHead of Data Management & Reporting Team

Commented:
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.

Author

Commented:
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 UngureanuHead of Data Management & Reporting Team

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.