locked
Commit size in OLEDB while inserting in to oracle RRS feed

  • Question

  • Hi Folks,

    I'm grateful if you could help me on the below.

    I have a staging table in SQL Server. I need to transfer the records in the Staging table of SQL server in to Staging table of Oracle database.

    I'm currently using OLEDB to transfer the records from SQL server to Oracle and I do understand  that it doesnt support Bulk insert for oracle.

    But the problem that I'm seeing is no of commits is more in oracle when I'm using OLEDB


    Is there a way to mention the batch size in oleDb? Or suggest me some way to do a bulk insert in to oracle with rowsize.

    Note: Please dont suggest Oracle Attunity as the option is completely ruled out by business as it requires Enterprise edition of SQL Server.

    Desperately looking for your replies.




    • Edited by Prabhuvincent Friday, September 27, 2013 3:01 PM Wrongly Mentioned the official Server Name. Please accept it.
    Friday, September 27, 2013 2:57 PM

Answers

  • The stock Oracle OLEDB connector works on a row-by-row basis I trust this explains your question.

    Arthur My Blog

    • Proposed as answer by Mike Yin Thursday, October 3, 2013 7:25 AM
    • Marked as answer by Mike Yin Sunday, October 6, 2013 2:56 PM
    Friday, September 27, 2013 3:12 PM
  • Yes, your other option is then indeed using the non-blocking (Asynchronous) Script Component as per the document.

    Arthur My Blog

    • Proposed as answer by Mike Yin Thursday, October 3, 2013 7:24 AM
    • Marked as answer by Mike Yin Sunday, October 6, 2013 2:56 PM
    Tuesday, October 1, 2013 2:29 PM

All replies