Post reply

Automate loading data to SQL Server 2016 from Oracle DB

  • 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

     

  • November 5, 2021 at 4:17 pm

    #3948413

    This sounds like something SSIS would be good for.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 5, 2021 at 4:53 pm

    #3948431

    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

     

     

  • November 6, 2021 at 8:20 pm

    #3948690

    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)
    Intro to Tally Tables and Functions

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

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