Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP - Database Accesses →  Open SQL →  Open SQL - Write Accesses →  INSERT dbtab 

INSERT dbtab - source

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


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

Alternatives

1. ... wa ...

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

Effect

You can specify a non-table-like data object wa after FROM and VALUES. 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

... wa ...

Effect

After VALUES and FROM you can specify a non-table-like work area wa, from whose content a row is created for insertion in the database table. The work area must fulfill the prerequisites for use in Open SQL statements.

  • When specifying a work area that does not contain any reference variables for LOB Handles, the content of the row to be added is taken from the work area wa without taking its data type into consideration and without converting it from left to right according to the structure of the database table or the view.
  • When you specify a LOB handle structure, it must be constructed (in accordance with the prerequisites) exactly like the structure of the database table. The components of the work area that are not LOB Handle components are assigned directly to the corresponding columns of the new row. In the case of a LOB handle component of a read stream type, this type is created. In this case of a type for a locator: this must exist and is used as a source. For details, see LOB handles.

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 ABAP Dictionary.

By default, an automatic client handling is performed, which means that a client identifier specified in wa is not considered, but the current client is used instead. This has no effect on wa. You can switch off automatic client handling using the addition CLIENT SPECIFIED.


Notes

  • The work area wa should always be declared in relation to the database table or the view in the ABAP Dictionary. For the derivation of LOB handle structures, there are special additions to the statements TYPES and [CLASS-]DATA.
  • If you have a static specification of the database table or the view, an obsolete short form of the specification outside of classes is possible. This means that the specification of the work area using FROM wa in the variant without INTO can be omitted. The prerequisite is that a table work area dbtab for the respective database table or the view is declared using the statement TABLES. The runtime environment then adds the addition FROM dbtab to the statement INSERTimplicitly.


Example

Inserting a new airline 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

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

Effect

You can specify an internal table itab after FROM, from whose content multiple rows are created for insertion in the database table. The row type of the internal table must fulfill the prerequisites for use in Open SQLstatements.

The content of each row of the internal table is composed using the same rules as for a single work area wa with the exception that when inserting from an internal table locators operate as the source but no write streamscan be created.

If no row with the same primary key or with the same unique secondary index exists 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, no rows are inserted. However sy-subrc is still set to 0. The system field sy-dbcnt is set to the number of rows that are inserted.

If a row with the same primary key or the same unique secondary index 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:

  • Using ACCEPTING DUPLICATE KEYS

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

  • Handling an exception

    If the addition ACCEPTING DUPLICATE KEYS is not specified, the handleable exception CX_SY_OPEN_SQL_DB is raised. Rows continue to be inserted until the exception is raised. The number of inserted rows is undefined. The system fields sy-subrc and sy-dbcnt retain their previous value.

  • Runtime Errors

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


    • Notes

    • If the runtime error produced by inserting existing rows is prevented by handling an exception, you have to initiate a program-driven database rollback, instead of using the addition ACCEPTING DUPLICATE KEYS.
    • When an internal table is used, package by package processing causes only some of the rows being inserted to be visible to any read access running in parallel with the INSERT.