8 Replies Latest reply: Nov 16, 2007 7:31 AM by prajwal k RSS

ACCEPTING DUPLICATE KEYS

prajwal k
Currently Being Moderated

Hi all

Please explain this code.

INSERT ZRDSSTOCK FROM TABLE INDT1 <b>ACCEPTING DUPLICATE KEYS</b>

 

how this code works......  what is the use (work)  of ACCEPTING DUPLICATE KEYS statement

 

regards

Prajwal.k

  • Re: ACCEPTING DUPLICATE KEYS
    Currently Being Moderated

    INSERT dbtab - source

     

     

    Syntax

    ... { {VALUES wa}

        | {FROM wa|{TABLE itab [ACCEPTING DUPLICATE KEYS]}} }.

     

     

     

    Alternatives:

    1. ... {VALUES wa} | {FROM wa} ...

     

    2. ... FROM TABLE itab [ACCEPTING DUPLICATE KEYS] ...

     

     

     

    Effect

    After FROM and VALUES, you can specify a non-table-type data object wa. After FROM, you can also specify an internal table itab. The contents of the row(s) to be inserted are taken from these data objects.

     

     

     

    Alternative 1

    ... {VALUES wa} | {FROM wa} ...

     

     

     

     

    Effect

    If you specify a non-table-type work area wa, a row is created from its contents for insertion in the database table. The content of the row to be inserted is taken from the work area wa without taking its data type into consideration and without conversion from left to right according to the structure of the database table or the view. The work area has to meet the prerequisites for use in Open SQL statements.

     

    The new row is inserted in the database table if this does not already contain a row with the same primary key or the same unique secondary index. If it does, the row is not inserted and sy-subrc is set to 4.

     

    If a view is specified in target that does not include all columns in the database table, these are set to the type-related initial value or to the null value in the inserted rows. The latter applies only if, for the relevant database column, the attribute initial value is not selected in the ABAP Dictionary.

     

    Notes

    Work area wa should always be declared in the ABAP Dictionary with respect to the database table or the view.

     

    If the database table or the view is specified statically, then outside classes you do not have to specify the work area using FROM wa in the variant without INTO if a table work area dbtab is declared for the relevant database table or for the view with statement TABLES. The system then enhances the INSERT statement implicitly with the addition FROM dbtab.

     

     

     

    Example

    Inserting a new airline company in the database table SCARR.

     

    DATA scarr_wa TYPE scarr.

     

    scarr_wa-carrid   = 'FF'.

    scarr_wa-carrname = 'Funny Flyers'.

    scarr_wa-currcode = 'EUR'.

    scarr_wa-url      = 'http://www.funnyfly.com'.

     

    INSERT INTO scarr VALUES scarr_wa.

     

     

     

    Alternative 2

    ... FROM TABLE itab [ACCEPTING DUPLICATE KEYS] ...

     

     

     

     

    Effect

    If you specify an internal table itab, several rows are created from its content for insertion in the database table. A row for insertion into the table is taken from each row of the internal table according to the same rules as for a single work area Einf&#63692;genwa. The line type of the internal table has to meet the prerequisites for use in Open-SQL statements.

     

    If a row witht he same primary key or a same unique secondary index does not already exist in the database table for any of the rows to be inserted, all rows are inserted and sy-subrc is set to 0. If the internal table is empty, sy-subrc is also set to 0. The system field sy-dbcnt is always set to the number of rows that were actually inserted.

     

    If a row with the same primary key or a same unique secondary index already exists in the database table for one or more of the rows to be inserted, these rows cannot be inserted. In this situation, there are three possibilities:

     

    Use of ACCEPTING DUPLICATE KEYS

     

    If the addition ACCEPTING DUPLICATE KEYS is specified, all rows are inserted for which this is possible. The remaining rows are rejected and sy-subrc is set to 4. The system field sy-dbcnt is set to the number of lines that are inserted.

     

     

    Handling an exception

     

    If the addition ACCEPTING DUPLICATE KEYS is not specified, a treatable exception occurs CX_SY_OPEN_SQL_DB (it always occurs since Release 6.10). Rows are inserted until the exception occurs. The number of rows that are inserted is not defined. The system fields sy-subrc and sy-dbcnt retain their previous value.

     

     

    Runtime error

     

    If the addition ACCEPTING DUPLICATE KEYS is not specified and if the exception is not handled, then a runtime error occurs (it always occurs prior to Release 6.10). This executes a database rollback that reverses all changes to the current database LUW. This applies in particular to rows that were inserted before a double entry occurred.

     

     

    Note

    If the runtime error is prevented because an exception is being treated, instead of using addition ACCEPTING DUPLICATE KEYS, if required, you have to initiate a program-controlled database rollback.

  • Re: ACCEPTING DUPLICATE KEYS
    Naimesh Patel
    Currently Being Moderated

    With ACCEPTING DUPLICATE KEYS, you can update the table even if they have the duplicate keys

     

    Generally, if you don't use the DUPLICATE KEYS and your itab have data with the duplicate key, then your INSERT will return SY-SUBRC <> 0.

     

    And you will not able to save the data.

     

    If you don't want to use this syntax

    INSERT ZRDSSTOCK FROM TABLE INDT1 ACCEPTING DUPLICATE KEYS

     

    You can do like:

    SORT INDT1.
    DELETE ADJACENT DUPLICATES FROM INDT1 COMPARING PRIMARY_KEY.
    
    INSERT ZRDSSTOCK FROM TABLE INDT1.

     

     

    Regards,

    Naimesh Patel

  • Re: ACCEPTING DUPLICATE KEYS
    Sreejith Ramachandran
    Currently Being Moderated

    This avaoids system going for dump incase the record already exist in table.

    Here sy-subrc s set to 4 if insert fails but never go for dump

     

    *reward if answered

  • Re: ACCEPTING DUPLICATE KEYS
    Nikhil Jadhav
    Currently Being Moderated

    If the addition ACCEPTING DUPLICATE KEYS is specified, all rows are inserted for which if a row with the same primary key or a same unique secondary index already exists in the database table for one or more of the rows to be inserted

     

    The remaining rows are rejected and sy-subrc is set to 4. The system field sy-dbcnt is set to the number of lines that are inserted.

     

    Regards

    Nikhil

  • Re: ACCEPTING DUPLICATE KEYS
    nagaraj kumar nishtala
    Currently Being Moderated

    Hi,

     

    the statement

     

    INSERT ZRDSSTOCK FROM TABLE INDT1 ACCEPTING DUPLICATE KEYS

     

    it means that the ZTABLE (ZRDSSTOCK ) can have more than one same value..for example if some entry say 10 is thre then second time we can enter the value 10 into this table also thirdtime like this....

     

    Regards,

    Nagaraj

  • Re: ACCEPTING DUPLICATE KEYS
    Sunil Sawaikar
    Currently Being Moderated

    Hello,

    When you try to insert duplicate records in table it will give run time error. If one or more lines cannot be inserted because the database already contains a line with the same primary key, a runtime error occurs. To prevent the runtime error occurring by using the addition ACCEPTING DUPLICATE KEYS. In this case, the lines that would otherwise cause runtime errors are discarded, and SY-SUBRC is set to 4.

  • Re: ACCEPTING DUPLICATE KEYS
    Mahaboob Pathan
    Currently Being Moderated

    HI,

     

    Here is behaviour of Use of ACCEPTING DUPLICATE KEYS :

     

    If the addition ACCEPTING DUPLICATE KEYS is specified, all rows are inserted for which this is possible. The remaining rows are rejected and sy-subrc is set to 4. The system field sy-dbcnt is set to the number of lines that are inserted.

     

    You can do one thing if you want to insert row which are not already existing.

    Declare one internal table. use select single statement to fetch the same record from database based on primary or secondary key(you are using for insert based on internal table). If sy-subrc not equal to zero then row is not existing. then you can insert the record.

     

    reward if helpfull.

Actions