topic Re: Problem with Insert statement in Application Development and Automation Discussions

Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:40:18 GMT

Hi there,

I have a problem when updating the values into database table from workarea, I am using this statement to update it but when I execute it throwing me into dump. Can any one please help me out what is the error and what I need to change my coding.

INSERT INTO ZCAT3_MOV VALUES LW_ITAB.

Regards,

Alex

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:43:16 GMT

Insert will dump if record already exists.

Use Modify statement

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:44:02 GMT

INSERT LW_ITAB INTO ZCAT3_MOV .

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:45:06 GMT

But is a new record to be inserted, Can I use the modify statement for this?

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:46:49 GMT

If a record exists modify works as update otherwise it will insert a new record. Modify works for both.

Edited by: Phanindra Annaparthi on Apr 2, 2009 9:46 AM

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:52:32 GMT

Hi Phani,

I used this statement even its shooting me dump

MODIFY ZCAT3_MOV FROM table LT_ITAB.

Any suggestions?

Alex

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:54:20 GMT

Hi Kemmer,

I tried to use the statement but again the same probs. Any suggestions?

Alex

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:59:08 GMT

Use this...

INSERT INTO ZCAT3_MOV VALUES LW_ITAB accepting duplicates.

Kiran

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 07:59:50 GMT

Hi,

Use this syntex

"MODIFY <database table> from <work area>"

if you are inserting any new record then it will insert the new record and if the record is already there then it will modify the existing one.

And if you want to insert data from an internal table then use

"MODIFY <database table> from table <internal table>"

Edited by: Digvijay Singh Pawar on Apr 2, 2009 10:01 AM

Re: Problem with Insert statement

faisalatsap — Thu, 02 Apr 2009 08:00:50 GMT

Hi,

use the following.

MODIFY <TABLE NAME> from <Work Area>. " if using Work Area
MODIFY <TABLE NAME> from table <Internal Table>." if using Internal Table

Hope will help you,

Best Regards,

Faisal

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 08:04:58 GMT

Hi alex

u have to loop on internal table. and then use

eg.

loop at itab into wa.

modify ZCAT3_MOV from wa.

endloop.

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 08:07:51 GMT

Hi,

You can use Modify Statement eg,


MODIFY ZTABLE FROM WORK AREA.

If no record exits present in the work area then this

statement will insert that value else it will update

that value.

Hope it helps

Regards

Mansi

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 08:11:27 GMT

Hi Yogesh,

I tried but cannot execute it sucessfully?

Re: Problem with Insert statement

RoySayak — Thu, 02 Apr 2009 09:19:07 GMT

Hello Alex,

Here is some input from my side..

MODIFY <TABLE NAME> from <Work Area>. "From Work Area
MODIFY <TABLE NAME> from table <Internal Table>. "From Internal Table

When you modifying from Work Area:

When a wa work area that is not table-type is specified, which meets the requirements for use in Open SQL statements, a line is searched for in the database table that has the same content in the primary key as the corresponding beginning part of the work area.

If such a line is not found, a new line is inserted according to the same rules as for the INSERT statement.

If such a line is found, this line is overwritten according to the same rules as for the UPDATE statement.

If the change would lead to a double entry in a unique secondary index, then it is not executed and sy-subrc is set to 4.

E.g.

DATA message_wa TYPE t100. 

message_wa-sprsl = 'EN'. 
message_wa-arbgb = 'MYMSGCLASS'. 
message_wa-msgnr =  '111'. 
message_wa-text =  'Alex is always happy'. 

MODIFY t100 FROM message_wa.

When you modifying from Internal Table:

If an itab internal table is specified, the system processes all lines in the internal table according to the rules for the wa work area. The line type of the internal table has to meet the requirements for use in Open SQL statements.

If the change to a line in the internal table would lead to a double entry in a unique secondary index, the corresponding line is not inserted and sy-subrc is set to 4. If the internal table is empty, sy-subrc is set to 0. The sy-dbcnt system field is always set to the number of lines that were actually processed.

Using UPDATE statement:

UPDATE <TABLE NAME> from <Work Area>. "From Work Area
UPDATE <TABLE NAME> from table <Internal Table>. "From Internal Table

E.g. for UPDATE from internal table#

PARAMETERS: p_carrid TYPE sflight-carrid, 
            percent(1) TYPE p DECIMALS 0. 

DATA sflight_tab TYPE TABLE OF sflight. 
FIELD-SYMBOLS <sflight> TYPE sflight. 

SELECT * 
       FROM sflight 
       INTO TABLE sflight_tab 
       WHERE carrid = p_carrid AND 
             fldate = sy-datum. 

IF sy-subrc = 0. 
  LOOP AT sflight_tab ASSIGNING <sflight>. 
    <sflight>-price = 
      <sflight>-price * ( 1 - percent / 100 ). 
  ENDLOOP. 
ENDIF. 

UPDATE sflight FROM TABLE sflight_tab.

Best Regards,

Sayak

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 09:57:23 GMT

see the followig example

LOOP AT IT_SCHEME INTO WK_SCHEME.

INSERT INTO ZTMP_SCH VALUES WK_SCHEME.

ENDLOOP.

put insert statement in loop as per example

Edited by: krupa jani on Apr 2, 2009 11:58 AM

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 10:07:37 GMT

first u check the declaration of ur work area (or) internal table .

ur internal table (or) work area declaration should be in line with Table fields -- ZCAT3_MOV,, in terms of fields order of the fields as well as in terms of data type of the fields.

Re: Problem with Insert statement

Former Member — Thu, 02 Apr 2009 11:08:27 GMT

Hi Alex,

If the entry already exists in the ztable and if you try to insert the same record once again,

it will go into dump.

Please use the below statement, it will work fine (accepting duplicate keys is must to avoid dump), .

INSERT ZCAT3_MOV VALUES FROM TABLE LW_ITAB ACCEPTING DUPLICATE KEYS

IF sy-subrc = 0.

COMMIT WORK.

ENDIF.

Thanks & Regards,

Ramya.