How to Insert SAP table into a Remote Table with same structure (without LOOP... ENDLOOOP)

[Former Member]

I have a remote connection in DBCON (called "CONN1").

I am trying to INSERT * (all records) from SAP (DEFAULT connection) to a remote table (via DBCON connection "CONN1") with the same structure.

I have tried all kinds of syntax including:

EXEC SQL.

INSERT INTO CONN1.SCHEMA.TABLE_NAME

(SELECT * FROM TABLE_NAME)

ENDEXEC.

and I always get a short dump (SQL error).

My only success has been to insert all records from TABLE_NAME (in the DEFAULT SAP connection) into an internal table (ITAB) and then Connect to CONN1 and then LOOP at the ITAB and INSERT records into the Remote table one by one. But this is VERY SLOW. We need to be able to do a INSERT with Sub-Select as I described above in two different schemas (CONN1 and DEFAULT).

Any advice you can provide will be greatly appreciated!

Thank you very much, Chris T.

[Former Member]

I have a remote connection in DBCON (called "CONN1").

I am trying to INSERT * (all records) from SAP (DEFAULT connection) to a remote table (via DBCON connection "CONN1") with the same structure.

I have tried all kinds of syntax including:

EXEC SQL.

INSERT INTO CONN1.SCHEMA.TABLE_NAME

(SELECT * FROM TABLE_NAME)

ENDEXEC.

and I always get a short dump (SQL error).

My only success has been to insert all records from TABLE_NAME (in the DEFAULT SAP connection) into an internal table (ITAB) and then Connect to CONN1 and then LOOP at the ITAB and INSERT records into the Remote table one by one. But this is VERY SLOW. We need to be able to do a INSERT with Sub-Select as I described above in two different schemas (CONN1 and DEFAULT).

Any advice you can provide will be greatly appreciated!

Thank you very much, Chris T.

[Nawanandana]

Did you check this answer ? if its not going to work, share your code.

[Sandra_Rossi]

Never done it. The ABAP documentation says that only one database connection can be used at a time. If you want to do it in one SQL query as you propose, I would opt for a non-ABAP solution. An intermediate solution could use two SQL queries, first one to read the table into a file, and second one to load the table from the file, the file being in a shared directory.

[Roland_J]

I would try to use CL_SQL_CONNECTION for the insert-statements. This way I guess a SELECT...ENDSELECT would be possible and inside this loop, insert the data into the other connection created by CL_SQL_CONNECTION. Note that handling a SQL-connection in this way involves some effort...

[albanleong]

I have previously written a blog with a custom z-program that copies data from SAP ASE DB over to a secondary DB (HANA) using ABAP ADBC (Native SQL). I believe the same approach should work for your requirement.

https://blogs.sap.com/2017/10/28/replicating-data-into-hana-using-abap-adbc-native-sql/