Automate loading data to SQL Server 2016 from Oracle DB

Question

Automate loading data to SQL Server 2016 from Oracle DB

Abdullah_Madani

Old Hand

Points: 340
More actions
November 5, 2021 at 4:12 pm

#3948409

Hi SQL Server Mentors,
We have a table in SQL Server 2016 that is frequently feeded up with data from an Oracle DB table based on a Query. I do it manually thru SQL Server import and export wizard. However I need to automate it as a batch process or stored procedure. Please advise me with a preferred method or tool to use. I think something inbuilt or native to SQL server itself will be a good choice.
Please advise

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

This sounds like something SSIS would be good for.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

or a Stored proc with a straight sql to retrieve the data from a linked server into SQL Server

caveats - which also apply to SSIS - dataypes conversion between oracle and SQL Server are painly slow - as an example is better to convert a date in oracle to a string, transfer the string and then convert to date in sql than to transfer as date.

Similar for numbers.

Performance gains can be quite significant if the above is done

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

For performance reasons, I wouldn't use a "straight" SELECT from a linked server. Instead, I normally use OPENQUERY as the source for an INSERT INTO.

 INSERT INTO dbo.sometable
        (somecolumnlist)
 SELECT somecolumnlist
   FROM OPENQUERY(alinkedserverppointedatanoracleserver, 'SELECT somecolumnlist FROM someoracletable WHERE somecolumn = ''something''')
;

Combine that with what Frederico posted above and you can get some really decent performance. To be sure, the query should be written using Oracle syntax.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)