abap - How to import SQL Server table to itab?

Question

is it possible to import data in SQL Server table to internal table (itab) directly?

I want to use this itab in a SE37 function. thanks in advance

Answer 1

For your task you can use a function module (SE37) or you can use module pool, or a report (SE38, SE80). Check this report:

report zinsertdb.

data: oref   type ref to cx_root,
      p_conex type dbcon_name value 'YOUR_DB_CONNECTION_STRING', " Ask the basis for this
      lv_sw type c,
      errormessage type string.

data it_mydata type standard table of mara.

start-of-selection.

  perform connect.
  perform insert_into_db.
  perform disconnect.

form connect.
  try.
      EXEC SQL.
        CONNECT TO :p_conex
      ENDEXEC.
      lv_sw = 'X'.
    catch cx_sy_native_sql_error into oref.
      lv_sw = space.
      errormessage = oref->get_text( ).
  endtry.
endform.

form disconnect.
  clear errormessage.
  if lv_sw = 'X'.
    try.
        EXEC SQL.
          COMMIT
        ENDEXEC.
      catch cx_sy_native_sql_error into oref.
        errormessage = oref->get_text( ).     
    endtry.
  endif.
endform.

form insert_into_db.

   data wa_mydata like line of it_mydata.
   data zcount type i.

   select * from mara into corresponding fields of table it_mydata.

   if sy-subrc EQ 0.

     loop at it_mydata into wa_mydata.

        try .

            exec sql. 
              EXECUTE PROCEDURE sp_get_data ( IN :wa_mydata-EAN11, IN :wa_mydata-MEINS, OUT :zcount ) 
            endexec.

            if zcount eq 0.
              exec sql.
                INSERT INTO "Your_Database".dbo.[Your_table]
                (Field1, Field2, Field3, FieldN)
                 VALUES (:wa_mydata-matnr,
                         :wa_mydata-ean11,
                         :wa_mydata-matkl,
                         :wa_mydata-meins)
              endexec.
              lv_sw = 'X'.
            endif.

          catch cx_sy_native_sql_error into oref.
            lv_sw = space.
            errormessage = oref->get_text( ).   
        endtry.

     endloop.

   endif.

endform.

Hope it helps.