kpskumar27 avatar image
kpskumar27 asked

Options for pulling data from ORACLE to SQL

Hi All, We are using SQL Server 2012 and Our client is using ORACLE 12c. We have created a job which will pull the data from ORACLE server(client's server) to our SQL Server. We are using linked server for this. The volume of the data is around 20 GB. This job will be running once in a week. It hardly takes 2 days to pull the data to our environment over the internet. If there is any network fluctuation, the process will stop, again we need to initiate the job. As an alternate, we are planning to get the oracle backup from the client and restore it in our SQL server. Is it feasible? Your help is highly appreciated. Thanks in advance, K.P.Senthil Kumar
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
Of the 20GB you're pulling in every week, how much of it is exactly the same data that you've already pulled in previously? Is it (for example) 20BG of brand new data, or perhaps 19.99GB of the stuff you already have plus a handful of changes (INSERTs/UPDATEs/DELETEs)?
0 Likes 0 ·

1 Answer

Tom Staab avatar image
Tom Staab answered
There are alternatives, but restoring an Oracle backup into SQL Server is not one of them. One method we've had to resort to in the past is to extract data to text files and then read them back in, but I generally consider that a last resort. Do you have Oracle at your location? Would you be able to restore the Oracle database there and then use SSIS to copy the data to SQL Server? Even if you have to copy it over the internet, I recommend SSIS over using a linked server. Here's one potential scenario with SSIS if a table has an incrementing identity column: 1. Execute SQL Task: get the max value loaded so far and store in a variable 2. Data Flow: get data for values greater than the max loaded so far
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kpskumar27 avatar image kpskumar27 commented ·
No Tom, we don't have ORACLE server. I think Notepad wont work for us, since the data volume is huge. Yes, you got the point. We have a identity column on the oracle table and we were fetching the records as you said(max value loaded and stored in a variable...). The problem with this is oracle(source) table has millions of record and hence, writing a insert query with a "WHERE" condition over the identity column has become a tedious process. So now we are truncating the destination table every time and insert all the records from ORACLE to SQL(destination) table.
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
I'm confused why the query with the condition on the max value is becoming tedious. You should only have to write it once and have it parameterized using the variable. Regarding the text option, you don't need to use an actual text editor. You can export the Oracle table to text and then import it into SQL Server using tools in the 2 database systems.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.