Catching failed db inserts

[Former Member]

Hi,

A quickie...

If I use the statement

INSERT zSTOCKOUT FROM TABLE itab_stockouts.

how would I avoid a short dump if the table already contains a row with the unique key?

I tried following the statement by sy-subrc NE 0, but it still short dumps before it even evaluates my statement.

I don't want to go through the whole itab to do a check first if I can help it.

Regards

Charl

[Former Member]

Use ACCEPTING DUPLICATE KEYS

INSERT zSTOCKOUT FROM TABLE itab_stockouts ACCEPTING DUPLICATE KEYS .

Please reward points.

Thanks

Romit

Edited by: Romit Kewalramani on Jun 18, 2008 2:41 AM

[Former Member]

Hi,

The best way use the Modify statement .

If it finds the keys then it will update the record or it will inser a new record.

I hope this will help you.

Help children of U.N World Food Program by rewarding them and encourage others to answer your queries

[prasanth_kasturi]

hi,

Its better to do with MODIFY statemnt

Modify acts both as insert and update

if a row exists it acts as update otherwise as update

hope i am clear

regards

prasanth

[Former Member]

So there is no way to capture the error without a short dump?

I don't really need it to modify. If it is there it doesn't need to do anything.

Thanks for the suggestions though

[christine_evans]

>

> So there is no way to capture the error without a short dump?
> 
> I don't really need it to modify. If it is there it doesn't need to do anything.
> Thanks for the suggestions though

You can use a TRY ..... ENDTRY to catch the exception eg

* For use with class based exception CX_SY_OPEN_SQL_DB.
  DATA:
  ex_check_os       TYPE REF TO cx_sy_open_sql_db,
  ex_check_rs       TYPE REF TO cx_sy_native_sql_error,
  ex_result(200)    TYPE C,
  ex_text           TYPE STRING.

  CLEAR: ex_check_os, ex_text.
   SORT gt_zswift_map BY ebeln swift_line_no.
   TRY.
     INSERT zswift_map
     FROM TABLE gt_zswift_map.
     CATCH CX_SY_OPEN_SQL_DB INTO ex_check_os.
     ex_text = ex_check_os->get_text( ).
     ROLLBACK WORK.
     w_stop_job = 'X'.
     CONCATENATE 'ERROR' ex_text INTO ex_text SEPARATED BY SPACE.
     MESSAGE ex_text TYPE 'I'.
     IF w_stop_job = 'X'.
        RETURN.
     ENDIF.
   ENDTRY.

The other way of avoiding a dump would be to check for the existence of a record before trying to insert it, which is effectively (I would think) what MODIFY does. I would NEVER use ACCEPTING DUPLICATE ENTRIES, because I think you need to know whether a record is new or if it already exists.

[Former Member]

I like this TRY command, never seen it before.

That will work I think.

I'll give it a shot and read up about the command in general.

Although I am still puzzled why the help says INSERT sets sy-subrc to 4 when a duplicate is found, but you can't do anything about it.

Bet that try command will solve many such situations.

Thanks again, that does it for me.