EXEC SQL - External Database Insert multiple rows

[Former Member]

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

[sarbajitm]

Hi Dev,

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

Regards.

Sarbajit.

[Former Member]

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

[Clemenss]

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

[Former Member]

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.

[Former Member]

Hi

Is possible connect to external Oracle DB from SAP using your code without create/update TnsNames.Ora file?

Thank you

[Former Member]

I don't think so.  I spent some time trying to do this and had no luck.  I was able to update the TNS file using transaction CG3Z though.  From transaction AL11, my system had it listed under DIR_PROFILE --> oracle --> tnsnames.ora.  It would be best if you had your basis team change this file though.  Hope this helps.

[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

[rosenberg_eitan]

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.