Before inserting the record, how to check if the perticular record already exists or not

[former_member220286]

Dear All ,

I have a requirement that I need to check if there are existing records in a ztable .

If the record already exits in the ztable then display message like 'a record with same key already exits else I should insert .

INSERT zdelete_log FROM TABLE it_delete .

Above statement I am using for Inserting record in my ztable : zdelete_log .Actually I am trying to delete one entry from ztable whose record already exist in the zdelete_log .So I am getting dump on this insert query .I can use modify but I want only new records to be inserted in my zdelete_log .

Please guide on how do i check if the record already exists or not in the database. If the perticular record already exists then no need to insert it. Else, insert the record. How to achieve this functionality .

Regards

Deep

[Mathew_S72]

Hello,

just insert always everything with

INSERT zdelete_log FROM TABLE it_delete ACCEPTING DUPLICATES KEYS.

With ACCEPTING DUPLICAT KEYS ... it will not dump any more now and sy-subrc=4 if rows where already there, sy-dbcnt holds the newly inserted row count

Best regards,
Matthias

[former_member220286]

Thanks for your valuable reply .I have used this accepting duplicate keys .

INSERT zdelete_log FROM TABLE it_delete ACCEPTING DUPLICATES KEYS.

but my record got vanished .I want every single record whether it exists or do not exists in the zdelete_log .It should be recorded in my zdelete_log table .Overall every single entry which is going for deletion should be recorded in zdelete_log .

regards

Deep

[Mathew_S72]

if you like to check the existence of rows you can read existing entries and flag them for deletion in zdelete_log

SELECT * FROM zdelete_log 
  INTO TABLE @DATA(lt_existing_rows) 
   FOR ALL ENTRIES IN it_delete
 WHERE key = it_delete-key.

" here you can loop over it_delete and read lt_existing_rows with key
" if key is is found in lt_existing_rows, you can update DB row for deletion
LOOP AT it_delete ASSIGNING FIELD-SYMBOL(<ls_delete>).
  READ TABLE lt_existing_rows WITH KEY key = <ls_delete>-KEY 
    INTO DATA(ls_existing_row).
  IF sy-subrc = 0.
     ls_existing_row-delete_me = 'X'.
     UPDATE zdelete_log FROM ls_existing_row.
  ENDIF.
ENDLOOP.
COMMIT WORK.

[Former Member]

Hi Matthias,

I'm sorry for interrupting but can you plz tell me what this code you wrote above viz-

@DATA(lt_existing_rows)

does?

I think it is a HANA syntax but since I dont yet know HANA can you plz tell me the ABAP equivalent of that code?

Will be grateful if you share your knowledge with me please.

Regards,

Dip

[matt]

It's ABAP.

New syntax introduced in 7.4. Read the help on inline declaration.

[former_member186746]

Hi, just go to se38 CTRL+F8 then click ABAP news radiobutton and see all the new features.

[Matias_AV]

The @DATA(lt_existing_rows) creates an internal table with the data resulting from the select statement.

[muralikrishnan_vijayanp]

Hi Deep,

Please use select as below to check existence of a record in DB if you are on ABAP 7.4 or above.

Select single @abaptrue 
 from zdelete_log 
 into @data(lv_true) 
 where key = it_delete-key.
 If sy-subrc Eq 0.
  "Record Exists
 endif.

Thanks,

Muralikrishnan

[raghug]

Based on your comment to Matthias...

but my record got vanished .I want every single record whether it exists or do not exists in the zdelete_log .It should be recorded in my zdelete_log table .Overall every single entry which is going for deletion should be recorded in zdelete_log .

I would say that your first problem is that you have a bad primary key on your zdelete_log table. You need a key that will be unique even if multiple similar records are saved. Have you considered adding a timestamp or a counter to your primary key?

[former_member184158]

Hello Deep Bisht

you can use try catch to check if record exist without to select the data:

*... Try to insert the data to DB

TRY.
INSERT zdelete_log FROM TABLE it_delete . 
    CATCH cx_sy_open_sql_db INTO DATA(lr_exc).
      MESSAGE lr_exc->get_text( ) TYPE 'E'.
  ENDTRY.<br>

Best regards

Ebrahim

[matt]

insert does not throw a cx_sy_open_sql_db exception.

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abapinsert_dbtab.htm

[matt]

Apparently I was wrong.

[Sandra_Rossi]

That would make a even better documentation, if INSERT, UPDATE, etc., could have a tiny section for exceptions, with only a reference to Open SQL - Exceptions, that would avoid misinterpretations with quick glances. I think I have seen other places in the doc with same kind of issue where it's difficult to find the information because it's located in a more general section. (to horst.keller)

[former_member184158]

Hallo Matthew Billingham,

in the link, I have also read:

If a row could not be inserted when the results set of the embedded subquery as inserted (since a row with the same primary key or the same unique secondary index exists), a catchable exception of the class CX_SY_OPEN_SQL_DB is always raised and the system field sy-subrc is not set.

I have tried it and it throw an exception

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abapinsert_dbtab.htm

Best regards

Ebrahim

[sirin1999]

To check if data is available in a SAP HANA table before performing an insert operation, you can use a SELECT statement with appropriate conditions to retrieve the desired data. If the SELECT query returns any rows, it means that data already exists in the table. Based on this result, you can decide whether to proceed with the insert operation or not

DO BEGIN
IF (SELECT COUNT(*) FROM SCHEMA_NAME1.TABLE_NAME1 WHERE COLUMN1 = 'VALUE1') = 0 THEN
   INSERT INTO SCHEMA_NAME1.TABLE_NAME1
   (
      COLUMN1,
      COLUMN2
   )
   VALUES
   (
   VALUE1,
   VALUE2
   )
END IF;
END