INSERT with CONNECTION to an external Database.

[Former Member]

Hi All,

   I have to INSERT an internal table values to an external Oracle Database.  The Solution I saw in Forums are with CONNECTION Addition in INSERT option with DBCON.

insert TABLE_NAME@SCHEMA CONNECTION 'PAYPROD'

         from table gt_summary.

I am getting syntax error  that "TABLE_NAME@SCHEMA", is unknown.  It does not exist.

Can anyone help me an example with CONNECTION option in INSERT.

Regards,

Asha

[Former Member]

Hi Asha,

Schema should identify in the tcode DBACOCKPIT, at here seems you need,

insert TABLE_NAME CONNECTION ('PAYPROD')

           from table gt_summary.

Please go through this from Thomas, http://scn.sap.com/community/hana-in-memory/blog/2012/04/11/test

regards,

Archer

[Former Member]

Hi Archer,

  The response was helpful.. I change the syntax as mentioned above.. I am still getting the Syntax error, telling "Table not defined in Dictionary or DATA Statement.".  Below is the insert statement used.  I am able to insert values as single records.  But I have nearly 100,00 records to insert which cases performance...

INSERT opfa_walker_summary_temp_sap@opdev connection 'PAYPROD'

             from table gt_summary.

opdev - Schema name..

PAYPROD - Server Connection mentioned in DBACOCKPIT

opfa_walker_summary_temp_sap - Table to get inserted.

Regards,

Asha

[Former Member]

This message was moderated.

[Former Member]

Hi Archer,

   The INSERT CONNECTION was working as expected for the Oracle Version 11i.  But I need to insert into an Oracle Database with version 8...  I am looking for a work around..

Thank you,

Asha

[raphael_almeida]

I recommend you use the classes below to resolve your problem.

• cl_sql_connection;
• cl_sql_statement.

First of all, connect to the database using the cl_sql_connection class, in the same class can make connection ping, among other features. You must have configured on the SAP server TNSNAMES with data for connection to this docking station. Second thing to do is to configure SAP to read this connection, this can be done by DBCO transaction. To carry out connection tests, utilixar the DB6TRC transaction.

Connecting successfully to external database, you use the cl_sql_statement class, within this please enter your insert command.

After completing the insert, by the cl_sql_statement class you can call the commit work.

For learning purposes, I advise you to see the ADBC_DEMO program, everything said here and more is explained there.

Regards

[Former Member]

Hi Rapheal,

    thank you for the response.. I went through the programs.. It is talking about INSERT into table VALUES (''').. I am looking for Insert multiple rows at the same time.. Something like

Insert into table values (select field1, field 2....).  But the SELECT table belongs to SAP.

Regards,

Asha

[raphael_almeida]

Hi Asha,

Oks, I understand, so I pointed him to read the ADBC_DEMO program in it insertion examples of multiple lines.

Regards,

[VenkatRamesh_V]

Hi,

Try,

Checking your connectivity in program ADBC_TEST_CONNECTION

Hope it helpful,

Regards,

Venkat.V

[Former Member]

Thank you, every one... My DBA created a Connection link to that particular table in Oracle 8.0.  Once that is done, the INSERT is working.

Regards,

Asha