Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Writes →  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

A non-table-like data object wa can be specified after FROM and VALUES. After FROM, an internal table itab can also be specified. The contents of the row(s) to be inserted are taken from these data objects. The escape character @ should precede the workarea name or the internal table name (as should be the case with every host variable).


Note

Host variables without the escape character @ are obsolete. The escape character @ must be specified in the strict modes of the syntax check from Release 7.40, SP05.

Alternative 1

... wa ...

Effect

After VALUES and FROM, a non-table-like work area wa can be specified, 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 a LOB handle structure is specified, 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 columns of the database table in question, the attribute NOT NULL is not selected in the database.

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. Automatic client handling can be switched off using the addition CLIENT SPECIFIED.


Notes

  • The work area wa should always be declared in relation to the database table or the view in ABAP Dictionary. For the derivation of LOB handle structures, there are special additions of the statements TYPES and [CLASS-]DATA.
  • If the database table or the view is specified statically, 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

An internal table itab can be specified 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 SQL statements.

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, a program-driven database rollback must be initiated, 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.