How to insert test data into tables in SAP Cloud Platform, ABAP Environment

[Andre_Fischer]

Updates

•  25.20.2019 - Changed the code as suggested by dezmond06 so that it also supports tables with includes/appends.

 

 

Two days ago I saw the tweet of Uwe Fetzer whether there is a recommended way to upload test data to tables in SAP Cloud Platform, ABAP Environment (aka Steampunk).

Unfortunately, there is no recommended way, but we are working on it ;-).

Since there aren't any sophisticated techniques available yet I asked colleagues for ways how to upload test data into tables in SAP Cloud Platform, ABAP Environment and found the following workaround.

1.  Create a report in the source (ERP) system that reads the data and creates a list output using "write" statements that contains appropriate Open SQL INSERT statements.


2.  Create a table in the target system.


3.  Create a class in the target system and start the import via F9.

The source code of the report was developed such that it can run also in older ABAP systems.

And writing such old code was quite a challenge for our development. But here it is:

*&---------------------------------------------------------------------*

*& Report z_sqldump

*&---------------------------------------------------------------------*

*&

*&---------------------------------------------------------------------*

REPORT z_sqldump.



PARAMETERS p_table TYPE tablename.



START-OF-SELECTION.

  DATA: lv_tabname TYPE tabname.



  DATA: lo_tabtype     TYPE REF TO cl_abap_tabledescr,

        lo_struct_type TYPE REF TO cl_abap_structdescr,

        lr_data        TYPE REF TO data,

        lt_comp_tab    TYPE cl_abap_structdescr=>component_table,

        ls_comp_fld    TYPE cl_abap_structdescr=>component,

        lv_sql_dump    TYPE string,

        lv_tmp_str     TYPE string,

        lv_tmp_value   TYPE string.





  FIELD-SYMBOLS: <fs_tab>    TYPE ANY TABLE,

                 <fs_struct> TYPE any,

                 <fs_field>  TYPE any.



  lv_tabname = p_table.



  lo_struct_type ?= cl_abap_typedescr=>describe_by_name( lv_tabname ).

  "lt_comp_tab  = lo_struct_type->get_components( ).

  

  DATA(inc_comp_tab)  = lo_struct_type->get_included_view( ).

  lt_comp_tab = VALUE #( BASE lt_comp_tab FOR value IN inc_comp_tab ( name = value-name type = value-type ) ).



  lo_struct_type = cl_abap_structdescr=>create( lt_comp_tab ).

  lo_tabtype     = cl_abap_tabledescr=>create( lo_struct_type ).



  CREATE DATA lr_data TYPE HANDLE lo_tabtype.

  ASSIGN lr_data->* TO <fs_tab>.



  CREATE DATA lr_data TYPE HANDLE lo_struct_type.

  ASSIGN lr_data->* TO <fs_struct>.



  SELECT *

    FROM (lv_tabname)

    INTO CORRESPONDING FIELDS OF TABLE <fs_tab>.



  WRITE / 'INSERT '.

  WRITE lv_tabname.

  WRITE ' FROM TABLE @( VALUE #( '.

  LOOP AT <fs_tab> ASSIGNING <fs_struct>.





    WRITE / '('.

    LOOP AT lt_comp_tab INTO ls_comp_fld.

      ASSIGN COMPONENT ls_comp_fld-name OF STRUCTURE <fs_struct> TO <fs_field>.

      lv_tmp_str = <fs_field>.



      CONCATENATE '''' lv_tmp_str '''' INTO lv_sql_dump.

      CONCATENATE ls_comp_fld-name '=' lv_sql_dump INTO lv_sql_dump SEPARATED BY space.

      WRITE / lv_sql_dump.

    ENDLOOP.

    WRITE / ')'.



  ENDLOOP.

  WRITE / ') ).'.





  CONCATENATE ' SELECT * FROM ' lv_tabname 'INTO TABLE @DATA(lt_sql_entries). ' INTO lv_sql_dump SEPARATED BY space.

  WRITE / lv_sql_dump.

  WRITE / ' IF sy-subrc = 0. '.

  WRITE / '   DATA(numberofrecords) = lines( lt_sql_entries ). '.

  WRITE / '   out->write( numberofrecords &&  '' entries inserted successfully '' ). '.

  WRITE / ' ENDIF. '.

Data is read from the following table:

@EndUserText.label : 'Inventory data group_094'

@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE

@AbapCatalog.tableCategory : #TRANSPARENT

@AbapCatalog.deliveryClass : #A

@AbapCatalog.dataMaintenance : #LIMITED

define table zinventory_094 {

  key client       : abap.clnt not null;

  key inventory_id : abap.numc(6) not null;

  product_id       : snwd_product_id;

  @Semantics.quantity.unitOfMeasure : 'zinventory_094.quantity_unit'

  quantity         : snwd_quantity;

  quantity_unit    : snwd_quantity_unit;

  remark           : snwd_desc;

  created_at       : snwd_created_at;

  status           : snwd_so_item_atp_status_code;



}

 

The report takes the name of the table as a parameter from which we want to export the test data. The output can be saved as a local file as shown in the following screen shot.

 



Be sure to download it unconverted.



 

In the trial system of SAP Cloud Platform, ABAP environment we have to create the table if it has not been imported using abapGit.

Here I had to create a table manually using appropriate standard data elements since the data elements of the SAP NetWeaver EPM data model that I used in the ERP system are not present in SAP Cloud Platform, ABAP Environment.

If you want to generate the source code of the target table you can check my following blog where a similar code has been published that generates DDL source code and a structure.

How to generate the DDL source code for custom entities that are implemented by remote function call...

@EndUserText.label : 'Demo data upload'

@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE

@AbapCatalog.tableCategory : #TRANSPARENT

@AbapCatalog.deliveryClass : #A

@AbapCatalog.dataMaintenance : #LIMITED

define table zinventory_094 {

  key client       : abap.clnt not null;

  key inventory_id : abap.numc(6) not null;

  product_id       : abap.char(10);

  @Semantics.quantity.unitOfMeasure : 'zinventory_094.quantity_unit'

  quantity         : abap.quan(13,3);

  quantity_unit    : abap.unit(3);

  remark           : abap.char(255);

  created_at       : abap.tims;

  status           : abap.char(1);



}

Then I created the class that uses the interface and pasted the code that has been generated by our report into the main method.

CLASS z_insert_sqldump DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .



  PUBLIC SECTION.

    INTERFACES if_oo_adt_classrun.

  PROTECTED SECTION.

  PRIVATE SECTION.

ENDCLASS.







CLASS z_insert_sqldump IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.

    INSERT  zinventory_094                  FROM TABLE @( VALUE #(

       (

       client = '100'

       inventory_id = '000001'

       product_id = 'HT-1000'

       quantity = '2.000 '

       quantity_unit = 'EA'

       remark = 'Convertible'

       created_at = '20190917223103.4663930 '

       status = 'N'

       )

       (

       client = '100'

       inventory_id = '000002'

       product_id = 'HT-1007'

       quantity = '5.000 '

       quantity_unit = 'EA'

       remark = 'Business notebook'

       created_at = '20190917223103.4663930 '

       status = 'A'

       )

       (

       client = '100'

       inventory_id = '000003'

       product_id = 'HT-1010'

       quantity = '3.000 '

       quantity_unit = 'EA'

       remark = 'Developer notebook'

       created_at = '20190917223103.4663930 '

       status = 'A'

       )

       (

       client = '100'

       inventory_id = '000005'

       product_id = 'HT-1002'

       quantity = '44.000 '

       quantity_unit = ''

       remark = 'nnn'

       created_at = '20190926193737.2251350 '

       status = 'N'

       )

       ) ).



    SELECT * FROM zinventory_094 INTO TABLE @DATA(lt_sql_entries).

    IF sy-subrc = 0.

      DATA(numberofrecords) = lines( lt_sql_entries ).

      out->write( numberofrecords && ' entries inserted successfully ' ).

    ENDIF.



  ENDMETHOD.



ENDCLASS.

 

Running this class via F9 shows the following output.

 

 



and we can check the data using the data preview functionality in ADT.

 



 

I hope this helps you until we will provide a solution as part of the standards.

 

Best Regards,

Andre