INSERT dbtab - ABAP Keyword Documentation

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

INSERT dbtab

Quick Reference

Syntax

INSERT { INTO target [ connection] VALUES wa }
     | {     target [connection] FROM wa }
     | {      target [connection] FROM TABLE itab }
     | {      target [connection] FROM ( SELECT subquery_clauses [UNION ...] ) }.

Variants:

1. INSERT INTO target VALUES wa.

2. INSERT target FROM wa.

3. INSERT target FROM TABLE itab.

4. INSERT target FROM ( SELECT subquery_clauses [UNION ...] ).

Effect

The Open SQL statement INSERT inserts one or more rows in the database table or classic view specified in target. The inserted rows are taken from a work area wa, an internal table itab, or the results set of an embedded subquery SELECT subquery_clauses. The addition connection can be used to specify a secondary database connection.

If VALUES is used, there must be an INTO between INSERT and target. If FROM is used, INTO must not be specified.

System Fields

The statement INSERT sets the values of the system fields sy-subrc (see below) and sy-dbcnt. sy-dbcnt is set to the number of inserted rows. If an overflow occurs because the number or rows is greater than 2,147,483,647, sy-dbcnt is set to -1. If sy-subrc is 2 when inserting a LOB handle structure with a component for writer streams, sy-dbcnt is also set to -1 (meaning undefined).

Notes

The inserted rows are included permanently in the table in the next database commit. Up until this point, they can still be removed by a database rollback. The current isolation level defines whether the inserted data can be read into other database LUWs before or only after the database commit.
The statement INSERT sets a database lock until the next database commit or rollback. If used incorrectly, this can produce a deadlock.
The number of rows that can be inserted in the tables of a database within a database LUW is limited, since a database system can only manage a limited amount of locks and data in the rollback area.
Once rows have been inserted in a global temporary table, this table must be emptied again explicitly before the next implicit database commit using the Open SQL statement DELETE FROM without WHERE or using an explicit database commit or database rollback. If not, the runtime error COMMIT_GTT_ERROR is produced.
The statement INSERT cannot be applied to the system table TRDIR.

Variant 1

INSERT INTO target VALUES wa.

Variant 2

INSERT target FROM wa.

Effect

These two variants insert a single row contained in a work area wa. Both variants display the same behavior and only their syntax is different.

System Fields

These variants of the statement INSERT set the value of the system field sy-subrc as follows:

sy-subrc	Meaning
0	The row specified in the work area in wa was inserted.
2	When a LOB handle structure was specified with a component for writer streams, the non-LOB handle components were not yet written to the database, but instead are passed when the stream is closed, at the latest. Whether this situation occurs or not depends on the database. Refer to LOB handles.
4	The row specified in the work area in wa was not inserted, since a row with the same primary key or a unique secondary index exists in the database table.

Variant 3

INSERT target FROM TABLE itab.

Effect

This variant inserts the rows contained in an internal table itab.

Note

There is no syntax variant with VALUES for internal tables.

System Fields

This variant of the statement INSERT sets the value of the system field sy-subrc as follows:

sy-subrc	Meaning
0	All rows of the internal table itab were inserted or the internal table is empty.
4	The addition ACCEPTING DUPLICATE KEYS is specified and not all rows of the internal table were inserted, since a row with the same primary key or a unique secondary index exists in the database table.

Variant 4

INSERT target FROM ( SELECT subquery_clauses [UNION ...] ).

Effect

This variant inserts the rows of the result set of an embedded subquery SELECT subquery_clauses, where multiple result sets can be joined with UNION.

System Fields

This variant of the statement INSERT sets the value of the system field sy-subrc as follows:

sy-subrc	Meaning
0	All rows of the results set of the embedded subquery were inserted.
4	The results set of the embedded subquery is empty and no rows were inserted.

Note

If a row could not be inserted when the results set of the embedded subquery as inserted (since a row with the same primary key or the same unique secondary index exists), a catchable exception of the class CX_SY_OPEN_SQL_DB is always raised and the system field sy-subrc is not set.

Continue
INSERT dbtab - target
INSERT dbtab - source