Scenario : In many cases, we don't have privileges to execute DML operations [mainly update, delete and insert] on SAP<SID> schema’s tables using database user. In this case, we have to execute DML operations on SAP<SID> schema’s tables through ABAP Statements. In one of my project’s scenario, we have to replicate data from table [ABC] resides in ECCSLT schema into table [ZABC] resides in SAP<SID> schema [Let’s say schema SAPABC].In this case, we have used NATIVE SQL to fetch data from ABC, then put it into ABAP internal table then inserted into ZABC using ABAP statement. Let’s suppose, ABC has more than 200 columns and in order to form native SQL, we have to concatenate all the columns. We can use function module ‘'DDIF_FIELDINFO_GET’ to get the structure of the table,then form the native SQL accordingly.
Here is the program for the above use-case.
REPORT ZIMPORT_INTO_SAPABC.
DATA:
lv_sql TYPE string value ' select 000 as MANDT , ',
lo_stmt TYPE REF TO cl_sql_statement,
lo_result TYPE REF TO cl_sql_result_set,
lr_sql_data TYPE REF TO Data,
lx_root TYPE REF TO cx_root,
mo_hana_conn type ref to cl_sql_connection,
lv_no_of_length type i .
DATA: BEGIN OF INTTAB OCCURS 250.
INCLUDE STRUCTURE DFIES.
DATA: END OF INTTAB.
CREATE DATA lr_sql_data TYPE table of ZABC.
mo_hana_conn = cl_sql_connection=>get_connection( con_name = 'ABC' ).
TRY .
lo_stmt = mo_hana_conn->create_statement( ).
data TABLENM TYPE DDOBJNAME value 'ZABC'.
call function 'DDIF_FIELDINFO_GET'
exporting
tabname = TABLENM
LANGU = SY-LANGU
TABLES
DFIES_TAB = INTTAB
EXCEPTIONS
NOT_FOUND = 1
INTERNAL_ERROR = 2
OTHERS = 3.
if sy-subrc <> 0.
WRITE:/ 'Field name not found'.
endif.
LOOP AT INTTAB.
if INTTAB-fieldname ne 'MANDT' .
concatenate lv_sql ' ' SPACE INTTAB-fieldname ' , ' into lv_sql .
endif.
ENDLOOP.
lv_no_of_length = STRLEN( lv_sql ).
lv_no_of_length = lv_no_of_length - 1 .
lv_sql = substring( val = lv_sql off = 0 len = lv_no_of_length ) .
concatenate lv_sql SPACE ' from ' SPACE ' ECCSLT.' ABC’ into lv_sql .
lo_result = lo_stmt->execute_query( statement = lv_sql ).
lo_result->set_param_table( itab_ref = lr_sql_data ).
lo_result->next_package( ).
lo_result->close( ).
field-symbols: <ls_sql> type any table.
assign lr_sql_data->* to <ls_sql>.
delete from ZABC.
modify ZABC from table <ls_sql> .
CATCH cx_root INTO lx_root.
RAISE EXCEPTION TYPE /iwbep/cx_mgw_tech_exception
EXPORTING
previous = lx_root.
ENDTRY.
