topic Re: EXEC SQL - External Database Insert multiple rows in Application Development and Automation Discussions

EXEC SQL - External Database Insert multiple rows

Former Member — Thu, 26 Mar 2009 12:52:08 GMT

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

Re: EXEC SQL - External Database Insert multiple rows

sarbajitm — Thu, 26 Mar 2009 13:26:22 GMT

Hi Dev,

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

Regards.

Sarbajit.

Re: EXEC SQL - External Database Insert multiple rows

Former Member — Wed, 16 Mar 2011 17:05:09 GMT

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

Re: EXEC SQL - External Database Insert multiple rows

Clemenss — Wed, 16 Mar 2011 21:19:23 GMT

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

Re: EXEC SQL - External Database Insert multiple rows

Former Member — Wed, 24 Aug 2011 15:05:24 GMT

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.

Re: EXEC SQL - External Database Insert multiple rows

Former Member — Mon, 19 May 2014 19:35:38 GMT

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

Thank you

Re: EXEC SQL - External Database Insert multiple rows

Former Member — Wed, 21 May 2014 22:35:05 GMT

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.

Re: EXEC SQL - External Database Insert multiple rows

Former Member — Wed, 28 May 2014 06:36:29 GMT

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

Re: EXEC SQL - External Database Insert multiple rows

rosenberg_eitan — Wed, 28 May 2014 06:56:13 GMT

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.