topic Re: How to Insert SAP table into a Remote Table with same structure (without LOOP... ENDLOOOP) in Application Development and Automation Discussions

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

Former Member — Wed, 04 Dec 2019 19:31:03 GMT

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.

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

Nawanandana — Wed, 04 Dec 2019 19:48:18 GMT

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

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

Sandra_Rossi — Thu, 05 Dec 2019 06:49:38 GMT

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.

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

Roland_J — Tue, 10 Dec 2019 09:11:28 GMT

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...

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

albanleong — Thu, 02 Jan 2020 21:50:03 GMT

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/