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
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
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.
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.
19 People are following this question.
