none
fast loading of data from a linked server ORACLE in MSSQL RRS feed

  • Question

  • I have procedures in DB MSSQL to load data from a linked server ORACLE
    in the DB MSSQL there is a tables:
    1. table_1 contains a list of orders ID
    2. table_2 contains detailed information on orders
    In DB MSSQL useds procedure to update and load data into a table_2, procedure uses an operator IN together with CURSOR , it works for a long time.
    table_2 contains 1-2 million rows.
    is there any other way / approach for quickly loading data from the ORACLE DBMS into MSSQL using the initial data from the table_1?
    Monday, October 21, 2019 1:48 PM

Answers

  • Hi

    If this is only being used for once (initial load) generate the data files in the Oracle system using an integration tool like SSIS and move those files to the SQL Server using SSIS and bulk load the data to the stage tables and load it into main tables.

    If this is recurring I would recommend to build a robust Integration system using integration tools like SSIS to move data with full functionality like Incremental logic & Restart-ability. I would not suggest using Linked Servers for the very large tables having huge volume as it's not meant for that.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Aleks Roth Wednesday, October 23, 2019 1:45 PM
    Monday, October 21, 2019 1:58 PM

All replies

  • Hi

    If this is only being used for once (initial load) generate the data files in the Oracle system using an integration tool like SSIS and move those files to the SQL Server using SSIS and bulk load the data to the stage tables and load it into main tables.

    If this is recurring I would recommend to build a robust Integration system using integration tools like SSIS to move data with full functionality like Incremental logic & Restart-ability. I would not suggest using Linked Servers for the very large tables having huge volume as it's not meant for that.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Aleks Roth Wednesday, October 23, 2019 1:45 PM
    Monday, October 21, 2019 1:58 PM
  • Hi Aleks Roth,

    I also advise you to use SSIS . For more information , please refer to Importing data from Oracle database to SQL Server using SSIS.

    If you have any issue about SSIS, please post your issue in the corresponding forum . SSIS Forum

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 7:33 AM
  • I would also check OpenQuery  command . It looks like OpenQuery runs the query completely on the remote server. The linked server call seems to run each statement on the remote sever and aggregates it locally.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 22, 2019 8:01 AM
    Answerer