ACCEPTING DUPLICATE KEYS

[Former Member]

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

[Former Member]

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.

[naimesh_patel]

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

[Former Member]

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

[Former Member]

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

[former_member404244]

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

[Former Member]

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.

[mahaboob_pathan]

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.

[Former Member]

thank u....