Update and insert in to table

[Former Member]

Hi All,

I am uploading data from a txt file into itab_infile and wants to update the table Zppprice based on itab_infile. It the record is existing it has to update and when it is a new record it has to insert the record. And I need to capture how many records havebeen updated and inserted.

Please help me.

Thanks,

Veni.

REPORT ZSD_PRICEPROTEC NO STANDARD PAGE HEADING
       LINE-SIZE 132
       LINE-COUNT 60.
 
TABLES: KNA1,ZPPPRICE.

TYPES: BEGIN OF type_infile,
         KUNNR(10) TYPE c,  "Customer Number
         MATNR(18) TYPE c,  "Material Number
         CRDATE(10) TYPE c,  "Creation Date
         EFDATE(10) TYPE c,  "Effective date
         SPRICE(15) TYPE c,  "Old Price
         EPRICE(15) TYPE c,  "New Price
         EOHQTY(09) TYPE c,  "Estimated Quantity
         AOHQTY(09) TYPE c.  "Actual Quantity
      TYPES: END OF type_infile.

* Internal tables
DATA: itab_infile TYPE STANDARD TABLE OF type_infile with header line.

* Work areas
DATA: wa_infile TYPE type_infile.

* Global variables.
DATA:  gc_tcode  LIKE sy-tcode VALUE 'VA01'. "Transaction code

SELECTION-SCREEN BEGIN OF BLOCK one WITH FRAME TITLE text-001.

PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK one.

START-OF-SELECTION.

  PERFORM upload_text_file.

*&---------------------------------------------------------------------*
*&      Form  UPLOAD_TEXT_FILE
*&---------------------------------------------------------------------*

FORM upload_text_file.

  CALL FUNCTION 'WS_UPLOAD'
       EXPORTING
            filename                = p_file
            filetype                = 'DAT'
       TABLES
            data_tab                = itab_infile
       EXCEPTIONS
            conversion_error        = 1
            file_open_error         = 2
            file_read_error         = 3
            invalid_type            = 4
            no_batch                = 5
            unknown_error           = 6
            invalid_table_width     = 7
            gui_refuse_filetransfer = 8
            customer_error          = 9
            OTHERS                  = 10.

  IF sy-subrc <> 0.

    MESSAGE e208(00) WITH 'Error in loading text file'.

  ENDIF.

ENDFORM.                    " UPLOAD_TEXT_FILE

[Former Member]

Hi Veni,

Simple thing ,you can use MODIFY Command,it works both insert as well update.

check the documentation.

see the below program and i am getting data from XLS file and i am updating to Ztable( same as ur case ).

************************************************************************

• Program : ZLWMI151_UPLOAD(Data load to ZBATCH_CROSS_REF Table)

• Type : Upload program

• Author : Seshu Maramreddy

• Date : 05/16/2005

• Transport : DV3K919574

• Transaction: None

• Description: This program will get the data from XLS File

• and it upload to ZBATCH_CROSS_REF Table

************************************************************************

REPORT ZLWMI151_UPLOAD no standard page heading

line-size 100 line-count 60.

*tables : zbatch_cross_ref.

data : begin of t_text occurs 0,

werks(4) type c,

cmatnr(15) type c,

srlno(12) type n,

matnr(7) type n,

charg(10) type n,

end of t_text.

data: begin of t_zbatch occurs 0,

werks like zbatch_cross_ref-werks,

cmatnr like zbatch_cross_ref-cmatnr,

srlno like zbatch_cross_ref-srlno,

matnr like zbatch_cross_ref-matnr,

charg like zbatch_cross_ref-charg,

end of t_zbatch.

data : g_repid like sy-repid,

g_line like sy-index,

g_line1 like sy-index,

$v_start_col type i value '1',

$v_start_row type i value '2',

$v_end_col type i value '256',

$v_end_row type i value '65536',

gd_currentrow type i.

data: itab like alsmex_tabline occurs 0 with header line.

data : t_final like zbatch_cross_ref occurs 0 with header line.

selection-screen : begin of block blk with frame title text.

parameters : p_file like rlgrap-filename obligatory.

selection-screen : end of block blk.

initialization.

g_repid = sy-repid.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = g_repid

IMPORTING

FILE_NAME = p_file.

start-of-selection.

• Uploading the data into Internal Table

perform upload_data.

perform modify_table.

top-of-page.

CALL FUNCTION 'Z_HEADER'

• EXPORTING

• FLEX_TEXT1 =

• FLEX_TEXT2 =

• FLEX_TEXT3 =

.

&----

---

*& Form upload_data

&----

---

• text

----

---

FORM upload_data.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

I_BEGIN_COL = $v_start_col

I_BEGIN_ROW = $v_start_row

I_END_COL = $v_end_col

I_END_ROW = $v_end_row

TABLES

INTERN = itab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

write:/10 'File '.

ENDIF.

if sy-subrc eq 0.

read table itab index 1.

gd_currentrow = itab-row.

loop at itab.

if itab-row ne gd_currentrow.

append t_text.

clear t_text.

gd_currentrow = itab-row.

endif.

case itab-col.

when '0001'.

t_text-werks = itab-value.

when '0002'.

t_text-cmatnr = itab-value.

when '0003'.

t_text-srlno = itab-value.

when '0004'.

t_text-matnr = itab-value.

when '0005'.

t_text-charg = itab-value.

endcase.

endloop.

endif.

append t_text.

ENDFORM. " upload_data

&----

---

*& Form modify_table

&----

---

• Modify the table ZBATCH_CROSS_REF

----

---

FORM modify_table.

loop at t_text.

t_final-werks = t_text-werks.

t_final-cmatnr = t_text-cmatnr.

t_final-srlno = t_text-srlno.

t_final-matnr = t_text-matnr.

t_final-charg = t_text-charg.

t_final-erdat = sy-datum.

t_final-erzet = sy-uzeit.

t_final-ernam = sy-uname.

t_final-rstat = 'U'.

append t_final.

clear t_final.

endloop.

delete t_final where werks = ''.

describe table t_final lines g_line.

sort t_final by werks cmatnr srlno.

• Deleting the Duplicate Records

perform select_data.

describe table t_final lines g_line1.

modify zbatch_cross_ref from table t_final.

if sy-subrc ne 0.

write:/ 'Updation failed'.

else.

Skip 1.

Write:/12 'Updation has been Completed Sucessfully'.

skip 1.

Write:/12 'Records in file ',42 g_line .

write:/12 'Updated records in Table',42 g_line1.

endif.

delete from zbatch_cross_ref where werks = ''.

ENDFORM. " modify_table

&----

---

*& Form select_data

&----

---

• Deleting the duplicate records

----

---

FORM select_data.

select werks

cmatnr

srlno from zbatch_cross_ref

into table t_zbatch for all entries in t_final

where werks = t_final-werks

and cmatnr = t_final-cmatnr

and srlno = t_final-srlno.

sort t_zbatch by werks cmatnr srlno.

loop at t_zbatch.

read table t_final with key werks = t_zbatch-werks

cmatnr = t_zbatch-cmatnr

srlno = t_zbatch-srlno.

if sy-subrc eq 0.

delete table t_final .

endif.

clear: t_zbatch,

t_final.

endloop.

ENDFORM. " select_data

Thanks

Seshu

[Former Member]

Thank you All for your suggestions.

Hi Seshu,

I tried similar way you suggested. It is inserting new entries into ztable ZPPPRICE from the Excel file, but it is not updating the records, if there are any changes. Ex: Like change in qty field.

Please guide me.

Thankyou.

Veni.

[Former Member]

Hi Veni,

It should work.

Modify command ( if new entries then it will insert the records),if record already exists in database then it will update non primary keys.

Just check with in debugging .

Thanks

Seshu

[Former Member]

Hi Seshu,

I debug the program and in t_final all the updated values are there, but after perform select data to delete the duplicate records, it is deleting all the records and no records are getting updated. Do I have to select all fields in FORM select_data.

How can I display lines in table also.

Please help me.

Thanks,

Veni.

[code]

REPORT ZSD_PRICEPROTEC NO STANDARD PAGE HEADING

LINE-SIZE 132

LINE-COUNT 60.

TYPES: BEGIN OF type_infile,

KUNNR(10) TYPE c, "Customer Number

MATNR(18) TYPE c, "Material Number

CRDATE(10) TYPE c, "Creation Date

EFDATE(10) TYPE c, "Effective date

SPRICE(15) TYPE c, "Old Price

EPRICE(15) TYPE c, "New Price

EOHQTY(09) TYPE c, "Estimated Quantity

AOHQTY(09) TYPE c. "Actual Quantity

TYPES: END OF type_infile.

TYPES: BEGIN OF type_zppprice,

KUNNR LIKE ZPPPRICE-KUNNR, "Customer Number

MATNR LIKE ZPPPRICE-MATNR, "Material Number

CRDATE LIKE ZPPPRICE-CRDATE, "Creation Date

EFDATE LIKE ZPPPRICE-EFDATE, "Effective date

SPRICE LIKE ZPPPRICE-SPRICE, "Old Price

EPRICE LIKE ZPPPRICE-EPRICE, "New Price

EOHQTY LIKE ZPPPRICE-EOHQTY, "Estimated Quantity

AOHQTY LIKE ZPPPRICE-AOHQTY. "Actual Quantity

TYPES: END OF type_zppprice.

data : g_repid like sy-repid,

g_line like sy-index,

g_line1 like sy-index,

$v_start_col type i value '1',

$v_start_row type i value '2',

$v_end_col type i value '256',

$v_end_row type i value '65536',

gd_currentrow type i.

DATA: itab_infile TYPE STANDARD TABLE OF type_infile with header line,

itab_zppprice TYPE STANDARD TABLE OF type_zppprice with header line.

data: itab like alsmex_tabline occurs 0 with header line.

data : t_final like zppprice occurs 0 with header line.

selection-screen : begin of block blk with frame title text.

parameters : p_file like rlgrap-filename obligatory.

selection-screen : end of block blk.

initialization.

g_repid = sy-repid.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = g_repid

IMPORTING

FILE_NAME = p_file.

start-of-selection.

• Uploading the data into Internal Table

perform upload_data.

perform modify_table.

&----

---

*& Form upload_data

&----

---

• text

----

---

FORM upload_data.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

I_BEGIN_COL = $v_start_col

I_BEGIN_ROW = $v_start_row

I_END_COL = $v_end_col

I_END_ROW = $v_end_row

TABLES

INTERN = itab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

write:/10 'File '.

ENDIF.

if sy-subrc eq 0.

read table itab index 1.

gd_currentrow = itab-row.

loop at itab.

if itab-row ne gd_currentrow.

append itab_infile.

clear itab_infile.

gd_currentrow = itab-row.

endif.

case itab-col.

when '0001'.

itab_infile-KUNNR = itab-value.

when '0002'.

itab_infile-MATNR = itab-value.

when '0003'.

itab_infile-CRDATE = itab-value.

when '0004'.

itab_infile-EFDATE = itab-value.

when '0005'.

itab_infile-SPRICE = itab-value.

when '0006'.

itab_infile-EPRICE = itab-value.

when '0007'.

itab_infile-EOHQTY = itab-value.

when '0008'.

itab_infile-AOHQTY = itab-value.

endcase.

endloop.

endif.

append itab_infile.

ENDFORM. " upload_data

&----

---

*& Form modify_table

&----

---

• Modify the table ZBATCH_CROSS_REF

----

---

FORM modify_table.

loop at itab_infile.

t_final-KUNNR = itab_infile-KUNNR.

t_final-MATNR = itab_infile-MATNR.

t_final-CRDATE = itab_infile-CRDATE.

t_final-EFDATE = itab_infile-EFDATE.

t_final-SPRICE = itab_infile-SPRICE.

t_final-EPRICE = itab_infile-EPRICE.

t_final-EOHQTY = itab_infile-EOHQTY.

t_final-AOHQTY = itab_infile-AOHQTY.

append t_final.

clear t_final.

endloop.

delete t_final where MATNR = ''.

describe table t_final lines g_line.

sort t_final by KUNNR MATNR CRDATE.

• Deleting the Duplicate Records

perform select_data.

describe table t_final lines g_line1.

modify ZPPPRICE from table t_final.

if sy-subrc ne 0.

write:/ 'Updation failed'.

else.

Skip 1.

Write:/12 'Updation has been Completed Sucessfully'.

skip 1.

Write:/12 'Records in file ',42 g_line .

write:/12 'Updated records in Table',42 g_line1.

endif.

delete from ZPPPRICE where MATNR = ''.

ENDFORM. " modify_table

&----

---

*& Form select_data

&----

---

• Deleting the duplicate records

----

---

FORM select_data.

select KUNNR

MATNR

CRDATE from ZPPPRICE

into table ITAB_ZPPPRICE for all entries in t_final

where KUNNR = t_final-KUNNR

and MATNR = t_final-MATNR

and CRDATE = t_final-CRDATE.

sort ITAB_ZPPPRICE by KUNNR MATNR CRDATE.

loop at ITAB_ZPPPRICE.

read table t_final with key KUNNR = ITAB_ZPPPRICE-KUNNR

MATNR = ITAB_ZPPPRICE-MATNR

CRDATE = ITAB_ZPPPRICE-CRDATE.

if sy-subrc eq 0.

delete table t_final .

endif.

clear: ITAB_ZPPPRICE,

t_final.

endloop.

ENDFORM. " select_data

[/code]

[Former Member]

Are you comparing with key fields :

select KUNNR

MATNR

CRDATE from ZPPPRICE

into table ITAB_ZPPPRICE for all entries in t_final

<b> where KUNNR = t_final-KUNNR

and MATNR = t_final-MATNR

and CRDATE = t_final-CRDATE.</b>

if not try to use key fields in where clause,you will not get any issue.

Thanks

Seshu

[Former Member]

Hi Seshu,

Yes I am comparing. The following Select Stmt I am using. I have the fields KUNNR, MATNR, CRDATE, EFDATE, SPRICE, EPRICE, EOHQTY, AOHQTY, CRMEMO. And I changed the fields EOHQTY and AOHQTY, these are changing in T_Final and after that deleting with the select stmt.

select KUNNR

MATNR

CRDATE from ZPPPRICE

into table ITAB_ZPPPRICE for all entries in t_final

where KUNNR = t_final-KUNNR

and MATNR = t_final-MATNR

and CRDATE = t_final-CRDATE.

Please guide me what I am doing wrong her.

Thank you.

Veni.

[Former Member]

Hi Veni,

What ever you written the code is creating the new reord to database table.if you compare the data with table and delete the table ,then it will delete the data.

so you can omment the perform routine and execute the program,now it will update the data into database.

comment below code ( I mean form routine ),now it updates as well insert new records.

select KUNNR

MATNR

CRDATE from ZPPPRICE

into table ITAB_ZPPPRICE for all entries in t_final

where KUNNR = t_final-KUNNR

and MATNR = t_final-MATNR

and CRDATE = t_final-CRDATE.

Good luck and i am sure you will get output.

Thanks

Seshu

[Former Member]

Thank you Seshu.

I really appriciate your help.

Regards,

Veni.

[Former Member]

Change <b>WS_UPLOAD</b> for <b>GUI_UPLOAD</b>

To update or insert into the table, use this...

MODIFY Zppprice FROM TABLE itab_infile.

Modify works like this....Is the record exists, it updates it...If the record does not exist, it inserts it -:)

Greetings,

Blag.

[Former Member]

You need to provide what are the keys in your custom table.

In general:

Loop at itab_infile. 
 modify zppprice from itab. " if your custom table and itab_infile has the same structure
Endloop.

Or if they have different structure, just replace the modify with the follwoing codes:

  select single * from ZPPPRICE into wa
    <conditions> 
  if sy-subrc <> 0.            " no entry found in custom table
    move-corresponding: itab_infile to wa.
    insert ZPPRICE from wa.
  else.                              " if entry found in custom table
     move
     modify ZPPPRICE from wa.  
  endif.

<b><i>Points are always welcome!</i></b>

Minami

[Former Member]

Hi,

1. Define two variables one for No of Inserts (v_insert) and one for No of Updates.

2. After PERFORM upload_text_file, loop the internal table.

3. Inside the loop, use INSERT.

4. Check sy-subrc after INSERT.

If sy-subrc = 0 - Current record was inserted

If sy-subrc = 4 - Current record could not be inserted, because the database table already contains a row with the same primary key or a unique secondary index.

5. If sy-subrc = 0, increment v_insert by 1 and Exit.

6. If sy-subrc = 4, use UPDATE to change the existing record and increment v_update = 1.

7. After the loop is over, v_index will give you how many records have been inserted and v_update will give you the no of records updated.

START-OF-SELECTION.

PERFORM upload_text_file.
PERFORM db_update.

*&---------------------------------------------------------------------*
*&      Form  db_update
*&---------------------------------------------------------------------*
FORM db_update.

  CLEAR : v_index, v_update.

  LOOP AT itab_infile.
    INSERT Zppprice FROM itab_infile.
    IF sy-subrc = 0.
      v_insert = v_insert + 1.
    ELSE.
      UPDATE Zppprice FROM itab_infile.
      v_update = v_update + 1.
    ENDIF.
  ENDLOOP.

ENDFORM.

Use GUI_UPLOAD instead of WS_UPLOAD as WS_UPLOAD is obsolete.

Reward points if the answer is helpful.

Regards,

Mukul

Message was edited by:

Mukul R. Kulkarni