topic Re: EXEC SQL: Internal table and loop needed to push records from SAP? in Application Development and Automation Discussions

EXEC SQL: Internal table and loop needed to push records from SAP?

Former Member — Wed, 14 Apr 2010 15:42:31 GMT

Hello,

I have a requirement to perform a table extract from SAP to External Database using DBCO config and Native SQL. The source system is Oracle ECC 6.0 and the target is an external MS SQL Server database (non-SAP).

Technical questions:

1) Do you have to use an internal table and loop to insert each record into the external database? Is it possible to pass an entire table or group of records in one call?

2) See any places for optimization here? I will look into using the PACKAGE SIZE to control the size of the internal tables, but it appears the bottleneck is the loop.

*Get source data and insert into ITAB

SELECT  *
  INTO TABLE lt_jest
  FROM jest.

*?????????????????????????????????????????????????????????????*
* #### IS IT POSSIBLE TO NOT LOOP OVER EACH RECORD HERE? #### *
*?????????????????????????????????????????????????????????????*

*Loop at ITAB and insert into SQL Server one record at a time

  LOOP AT lt_jest ASSIGNING <FS>.
    EXEC SQL.
      INSERT INTO [dbo].[JEST]
       VALUES (:<FS>-MANDT,:<FS>-OBJNR,:<FS>-STAT,:<FS>-INACT,:<FS>-CHGNR);
        commit;
    ENDEXEC.
  ENDLOOP.

Re: EXEC SQL: Internal table and loop needed to push records from SAP?

Former Member — Thu, 15 Apr 2010 04:22:55 GMT

Not sure why we would want to use ABAP to do this. If it were me, I'd ask Basis or DBA to extract and push to new table using SQL on the db server, not through an ABAP program.

Re: EXEC SQL: Internal table and loop needed to push records from SAP?

SuhaSaha — Thu, 15 Apr 2010 04:39:17 GMT

Hello,

SAP has provided standard API for connecting with external database ADBC. Refer: [ADBC - ABAP Database Connectivity |http://help.sap.com/abapdocu_70/en/ABENADBC.htm]

Instead of writing the Native SQL you can make use of the methods of the ADBC classes. Refer to the demo program ADBC_DEMO for details.