topic Re: Open sql insert not working in Application Development and Automation Discussions

Open sql insert not working

Former Member — Mon, 12 Jan 2009 11:33:13 GMT

Hello SdNers,

I was trying to insert records into table CSKA Cost Elements (Data Dependent on Chart of Accounts).

I have an excel file with 45 records.

I am able to upload to records from the file and display it on the list processing screens.

It is not getting inserted to DB table...CSKA.



data : begin of t_ks06 occurs 0,
       KOKRS type KOKRS,      " Controlling area
       KSTAR type KSTAR,      " Cost element
       DATAB type DATAB,      " Valid from date
       DATBI type DATBI,      " Valid to  date
       KTEXT type KTEXT,      " Name
       LTEXT type LTEXT,      " Description
       KATYP type KATYP,      " Cost element cat
       KOSTL type KOSTL,      " Cost center
       end of t_ks06.

data : wa like line of t_ks06.

data : it_excel type alsmex_tabline occurs 0 with header line.

selection-screen begin of block b1 with frame title text-001.
parameter: f_name type rlgrap-filename default 'C:\Documents and Settings\Administrator\Desktop\Project_data\Cost_element_KS06.xls'.
parameter : p_begcol type i default 1 no-display,
            p_begrow type i default 2 no-display,
            p_endcol type i default 8 no-display,
            p_endrow type i default 46 no-display.

selection-screen end of block b1.

at selection-screen on value-request for f_name.
perform f_get_file using f_name.

start-of-selection.
perform f_xls_itab using f_name changing it_excel.
perform f_move_data.
perform f_insert_db.
perform f_display_data.

form f_get_file  using    p_file_nam.

call function 'KD_GET_FILENAME_ON_F4'
 exporting
   program_name        = syst-repid
   dynpro_number       = syst-dynnr
*   FIELD_NAME          = ' '
*   STATIC              = ' '
*   MASK                = ' '
  changing
    file_name           = f_name
 exceptions
   mask_too_long       = 1
   others              = 2.
endform.                    " f_get_file

form f_xls_itab  using    p_file_nam changing p_it_excel.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  exporting
    filename                      = f_name
    i_begin_col                   = p_begcol
    i_begin_row                   = p_begrow
    i_end_col                     = p_endcol
    i_end_row                     = p_endrow
  tables
    intern                        = it_excel
 exceptions
   inconsistent_parameters       = 1
   upload_ole                    = 2
   others                        = 3.
endform.                    " f_xls_itab

form f_move_data.

data : lv_index type i.
field-symbols <fs>.

* Sorting the internal table
sort it_excel by row col.
clear it_excel.

loop at it_excel.
  move it_excel-col to lv_index.
* Assigning each record to the internal table row.
  assign component lv_index of structure wa to <fs>.

* Assigning the field value to a field symbol
  move it_excel-value to <fs>.

  at end of row.
  append wa to t_ks06.
*   flg_mv = 1.
  clear wa.
  endat.

endloop.
endform.                    " f_move_data

form f_display_data.

  write:/1 sy-uline(140).
  write:/1 sy-vline,  'Cont area',        " Controlling area
         16 sy-vline, 'Cost ele',         " Cost element
         31 sy-vline, 'Valid from',       " Valid from  date
         46 sy-vline, 'Valid to '  ,      " Valid to date
         61 sy-vline, 'Name',             "Cost element cat
         76 sy-vline, 'Description',      " Cost center
         101 sy-vline,  'Cost ele cat',   " Cost element cat
         126 sy-vline, 'Cost center',     " Cost center
         140 sy-vline.
 write:/1 sy-uline(140).

skip 1.
clear wa.
loop at t_ks06 into wa.

  write:/2 sy-uline(139).
  write:/2  sy-vline,  wa-KOKRS,      " Controlling area
         16 sy-vline, wa-KSTAR,      " Cost element
         31 sy-vline, wa-DATAB,      " Valid from date
         46 sy-vline, wa-DATBI,      " Valid to date
         61 sy-vline, wa-KTEXT,      " Name
         76 sy-vline, wa-LTEXT,      " Description
         101 sy-vline, wa-KATYP,     " Cost element cat
         120 sy-vline, wa-KOSTL,     " Cost center
         140 sy-vline.
endloop.

endform.                    " f_display_data

form f_insert_db .

if t_ks06[] is initial.
  write:/ 'No records fetched from file' color 3.
else.
  clear wa.
  loop at t_ks06 into wa.
  insert into CSKA values wa.
  endloop.
*    if sy-dbcnt ge 1.
*    perform f_display_data.
*    endif.
endif.

endform.                    " f_insert_db

Help highly appreciated and would be rewarded.

Regards,

Ranjith N

Re: Open sql insert not working

Former Member — Mon, 12 Jan 2009 11:38:55 GMT

The code should be:

INSERT CSKA FROM WA.

But WA has to be defined as CSKA.

Anyway y should use a BDC program or use BAPI or another std fm in order to upload the CSKA

Max

Re: Open sql insert not working

Former Member — Mon, 12 Jan 2009 11:39:31 GMT

Hi,

Try this way

insert into CSKA FROM wa.

Re: Open sql insert not working

christine_evans — Mon, 12 Jan 2009 11:40:50 GMT

Has anyone ever mentioned to you that it is usually a bad idea to do direct updates on SAP tables? The preferred option, which I think you should consider, is to find a SAP standard method of doing this - either a BADI or in the last resort a BDC on the update transaction.

Re: Open sql insert not working

Former Member — Mon, 12 Jan 2009 11:42:06 GMT

Hello Max,

Thanks for your reply as you said can you help me or guide me with the same.

Eg : FM or BAPI.

Regards,

Ranjith N

Re: Open sql insert not working

Former Member — Mon, 12 Jan 2009 11:44:27 GMT

Hi,

Instead of move statements, you can use insert statements.

Check out this link.

http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb3a6d358411d1829f0000e829fbfe/frameset.htm

Regards,

Deepthi.

Re: Open sql insert not working

Former Member — Mon, 12 Jan 2009 11:57:00 GMT

U can create a BDC program for trx KA01 or try to use the BAPI BAPI_COSTELEM_CREATEMULTIPLE

Max

Re: Open sql insert not working

Former Member — Mon, 12 Jan 2009 12:12:51 GMT

Hello Ranjinth,

To insert the record into data base table. you need to create a work area type of data base table.

because for insert purpose we need the all the field in header or in work area for inserting the new record into data base table. it doesn't mean that you need to use old the field that is avaible in data base table.

you can use only those field that you need in your program with primary key.Because primary key is mandatory to enter for inserting new record.

you can try this..

wa_wrk1 type CSKA.

to insert record.

INSERT CSKA FROM wa_work1.

hope it will help you.

Thanks.

Arun Kayal.

Re: Open sql insert not working

Former Member — Tue, 13 Jan 2009 07:35:13 GMT

ANSWERED