Skip to Content
avatar image
Former Member

EXEC SQL - External Database Insert multiple rows

Hi,

I'm looking for the right syntax to INSERT all the records in an internal table into an external Oracle database table in a single SQL statement. There are no issues with connectivity maintained thru DBCO.

I'm able to connect, retrieve, update, delete and insert records in the external db table.

Currently I'm looping though an internal table and inserting records one by one and this is time consuming

when there are thousands of records.

Would like to insert all the records in the internal table in a single SQL statement without having to

loop through the internal table.

Have been trying with the following statement but no luck so far:

INSERT INTO <ext.table> (fieldA, fieldB, fieldC)

SELECT sapfldA, sapfldB, sapfldC

FROM TABLE <sap.table>

-Dvas

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Mar 26, 2009 at 01:26 PM

    Hi Dev,

    Do some RnD with Oracle DML Merge statement. May you get some light on the issue.

    Regards.

    Sarbajit.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 16, 2011 at 05:05 PM

    Hi,

    I have the same problem. I should copy records from SAP table to EXT table using a DBCO.

    I don't understand last reply....

    could you help me?

    1. after the open connection to external DB, Can I access to SAP table using a EXEC SQL? How?

    2. Can I use this statement?

    INSERT INTO ZZ_MARA@DDDSVI

    (MATNR, LVORM)

    SELECT (MATNR, LVORM) FROM MARA WHERE MTART = :LV_MTART

    thank you so much.

    mauro

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 16, 2011 at 09:19 PM

    Hi Dvas,

    try like this ([bulk insert|http://www.dba-oracle.com/oracle_news/news_plsql_forall_performance_insert.htm])

    EXECUTE IMMEDIATE 'TRUNCATE TABLE products';
    Start_time := DBMS_UTILITY.get_time;
    FORALL i in products_tab.first .. products_tab.last
     INSERT INTO products VALUES products_tab(i);
    end_time := DBMS_UTILITY.get_time;
    DBMS_OUTPUT.PUT_LINE(u2018Bulk Insert: u2019||to_char(end_time-start_time));
    COMMIT;
    END;

    Regards,

    Clemens

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 24, 2011 at 03:05 PM

    I accomplished what you are asking by creating an Oracle dblink in the SAP database. Abaper's used to be able to do this in 4.5 and 4.7, but you may need to ask your Basis to do it in ECC6.0. This method does not need to use tcode DBCO and it does not need to have entries made in the tnsnames.ora file. Here is some code I used to create the DB link and transfer all data in SAP's FMIOI table to a table called TSAP_FMIOI in an external oracle database. This method transfers the data very quickly.

    This statement may need to be done at the Oracle level instead of using ABAP:

    EXEC SQL.

    CREATE DATABASE LINK xxxLINKNAMExxx CONNECT TO xxxDBUSERNAMExxx

    IDENTIFIED BY xxxDBPASSWORDxxx USING

    '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = xx.xx.xxx.xxx)

    (PORT = 1521)) (CONNECT_DATA = (SID = xxxx))'

    ENDEXEC.

    Here are the stmts I use to execute a stored procedure on the remote database and copy the FMIOI table to the remote table called TSAP_FMIOI:

    EXEC SQL.

    EXECUTE PROCEDURE SAP_PKG.TRUNCATE_TSAP_FMIOI@xxxDBLINKNAMExxx

    ENDEXEC.

    EXEC SQL.

    INSERT INTO TSAP_FMIOI@xxxDBLINKNAMExxx SELECT * FROM FMIOI

    WHERE MANDT = 100

    ENDEXEC.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi

      Thank,

      Connection is OK (select from table1 working). But I have a roblem with INSERT. Can you help me?

      This sample don't throw exception, but not insert data to table1.

      EXEC SQL.
      INSERT INTO TABLE1
      VALUES
      ( 'aaa', 'bbb' )
      ENDEXEC.


      Thank you

  • May 28, 2014 at 06:56 AM

    Hi,

    First of all I hope that the tables involve are none SAP tables (Z tables) .

    I did not try this myself but have a look at:


    http://scn.sap.com/community/abap/blog/2013/12/26/sdbadbc--the-program

    The usage of:

    cl_sql_connection
    cl_sql_statement
    cl_sql_result_set
    cx_sql_exception

    Regards.

    Add comment
    10|10000 characters needed characters exceeded