Inserting into the Database

Variants:

1. INSERT INTO  dbtab      VALUES wa. oder
   
INSERT INTO (dbtabname) VALUES wa. oder
   
INSERT  dbtab      FROM wa. oder
   
INSERT (dbtabname) FROM wa.

2. INSERT  dbtab      FROM TABLE itab. oder
   
INSERT (dbtabname) FROM TABLE itab.

3. INSERT  dbtab. oder
   
INSERT *dbtab.

Effect

Inserts new lines in a database table (see relational database). You can specify the name of the database table either in the program itself in the form dbtab or at runtime as the contents of the variable dbtabname. In both cases, the database table must be defined in the ABAP Dictionary. By default, data is only inserted in the current client. Data can only be inserted using a view if the view refers to a single table and was defined in the ABAP Dictionary with the maintenance status "No restriction".

INSERT belongs to the Open SQL command set.

In some cases, the syntax rules that apply to Unicode programs are different than those for non-Unicode programs.See Open SQL and Unicode.

Notes

  1. You cannot insert a line if a line with the same primary key already exists or if a UNIQUE index already has a line with identical key field values (with regard to this UNIQUE index).


  2. When inserting lines using a view, all fields of the
    database table that are not in the view are set to their initial value
    (see TABLES) - if they were defined with NOT NULL in the ABAP Dictionary. Otherwise they are set to NULL.


  3. Authorization checks (see The SAP Authorization Concept) are not supported by the INSERT statement. You must include these in the program yourself.


  4. Lines specified with the INSERT command are not finally added to the database table until after a database commit (see Logical Unit of Work (LUW)). Prior to this, you can cancel any changes to the database with a database rollback (see Programming Transactions).


  5. In the dialog system, you cannot rely on the locking mechanism used by the database system (see Database Locking) to synchronize simultaneous access to the same database by several users. Therefore, it is often necessary to use SAP's locking mechanism (see SAP Locking).


Variant 1

INSERT INTO dbtab      VALUES wa. or
INSERT INTO (dbtabname) VALUES wa. or

INSERT  dbtab      FROM wa. or
INSERT (dbtabname) FROM wa.

Extras:

1. ... CLIENT SPECIFIED

2. ... CONNECTION con

Effect

Inserts one line into a database table. The line to be inserted is taken from the work area wa and the data read from left to right according to the line structure of the database table dbtab. Here, the structure of wa is not taken into account. For this reason, the work area wa must be at least as wide (see DATA) as the line structure of dbtab, and the alignment of the work area wa must correspond to the alignment of the line structure. Otherwise, a runtime error occurs.
If the database table dbtab or the work area wa contain strings, wa must be compatible with the line structure of dbtab.

When the command has been executed, the system field SY-DBCNT contains the number of inserted lines (0 or 1).

The Return Code is set as follows:

SY-SUBRC = 0:
Line was successfully inserted.
SY-SUBRC = 4:
Line could not be inserted since a line with the same key already exists.

Example

Insert the customer Robinson in the current client:

DATA: wa TYPE scustom.
wa-id        = '12400177'.
wa-name      = 'Robinson'.
wa-postcode  = '69542'.
wa-city      = 'Heidelberg'.
wa-custtype  = 'P'.
wa-discount  = '003'.
wa-telephone = '06201/44889'.

INSERT INTO scustom VALUES wa.

Addition 1

... CLIENT SPECIFIED

Effect

Automatic client handling is switched off. This allows
you to process data on a cross-client basis for client-specific tables. The client field is then treated like a normal table field which the program must fill with values in the work area wa.

The addition CLIENT SPECIFIED must be specified directly after the name of the database table.

Example

Insert the customer Robinson in client 2:

DATA: wa TYPE scustom.
wa-mandt     = '002'.
wa-id        = '12400177'.
wa-name      = 'Robinson'.
wa-postcode  = '69542'.
wa-city      = 'Heidelberg'.
wa-custtype  = 'P'.
wa-discount  = '003'.
wa-telephone = '06201/44889'.

INSERT scustom CLIENT SPECIFIED FROM wa.

Addition 2

... CONNECTION con

Effect

The Open SQL command is not executed on the
standard database, but on the secondary database connection specified with con. con is the name of the databse connection as it was specified in the table DBCON in the column CON_NAME. The database connection con can also be specified dynamically in the form (source_text), where the field source_text contains the name of the database connection and must be type C or STRING. The CONNECTION con addition must be specified directly after the name of the database table or after the CLIENT SPECIFIED addition.

Variant 2

INSERT  dbtab      FROM TABLE itab. oder
INSERT (dbtabname) FROM TABLE itab.

Extras:

1. ... CLIENT SPECIFIED

2. ... ACCEPTING DUPLICATE KEYS

3. ... CONNECTION con

Effect

Mass insert: All lines of the internal table itab are inserted in one single operation. The lines of itab must fulfill the same conditions as the work area wa in variant 1.

When the command has been executed, the system field SY-DBCNT contains the number of inserted lines.

The Return Code is set as follows:

SY-SUBRC = 0:
All lines successfully inserted. Any other result causes a runtime error.

Note

If the internal table itab is empty, SY-SUBRC and SY-DBCNT are set to 0 after the call.

Addition 1

... CLIENT SPECIFIED

Effect

As with variant 1.


Addition 2

... ACCEPTING DUPLICATE KEYS

Effect

If a line cannot be inserted, the system does not
terminate with a runtime error but only sets the return value SY-SUBRC to 4. All other lines are inserted after the command is executed.

Addition 3

... CONNECTION con

Effect

As with variant 1.


Variant 3

INSERT  dbtab. or
INSERT *dbtab.

Extras:

1. ... CLIENT SPECIFIED

2. ... CONNECTION con

The syntax check performed in an ABAP Objects context is stricter than in other ABAP areas.
See Cannot Use Short Forms and Cannot Use * Work Areas.

Note

This variant is obsolete.

Effect

These are the SAP-specific short forms of variant 1. They have the same effect as variant 1, but the work area is not specified explicitly. Instead, the system implicitly uses the table work area dbtab or *dbtab declared using a TABLES statement.

is equivalent to

When the command has been executed, the system field SY-DBCNT contains the number of lines inserted (0 or 1).

The Return Code is set as follows:

SY-SUBRC = 0:
Line successfully inserted.
SY-SUBRC = 4:
Line could not be inserted, since a line with the same key already exists.

Example

Add a line to a database table:

TABLES sairport.
sairport-id   = 'NEW'.
sairport-name = 'NEWPORT APT'.

INSERT sairport.


Notes

  1. You should always use variant 1 where you specify an explicit work area wa instead of using the implicit TABLES work area.
  2. If a work area is not explicitly specified, the values for the line to be inserted are taken from the table work area dbtab if the statement is in a FORM or FUNCTION where the table work area is stored in a formal parameter or local variable of the same name.

Addition 1

... CLIENT SPECIFIED

Effect

As with variant 1.

Addition 2

... CONNECTION con

Effect

As with variant 1.


Additional help

Inserting Lines in Tables