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

INSERT dbtab

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

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

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 as an exclusive 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 ABAP 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

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 forwriter streams, the non-LOBhandle 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. See LOB handles.
4	The row specified in the work area in `wa` was not inserted, since a row with the sameprimary key or a uniquesecondary index exists in the database table.

Example

Inserts two individual rows in a database table.

DELETE FROM demo_expressions. 

DATA wa TYPE demo_expressions. 

wa = VALUE #( id = 'X' num1 = 111 ). 
INSERT INTO demo_expressions VALUES wa. 

wa = VALUE #( id = 'Y' num1 = 222 ). 
INSERT demo_expressions FROM wa.

A host expression with the value operator VALUE can be used instead of an explicit work area.

DELETE FROM demo_expressions. 

INSERT INTO demo_expressions VALUES @( VALUE #( id = 'X' num1 = 111 ) ). 

INSERT demo_expressions FROM @( VALUE #( id = 'X' num1 = 111 ) ).

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

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 sameprimary key or a uniquesecondary index exists in the database table.

Example

Inserts two individual rows in a database table.

DELETE FROM demo_expressions. 

DATA itab TYPE TABLE OF demo_expressions. 

itab = VALUE #( ( id = 'X' num1 = 111 ) 
                ( id = 'Y' num1 = 222 ) ). 
INSERT demo_expressions FROM TABLE itab.

A host expression with the value operator VALUE can be used instead of an explicit internal table.

DELETE FROM demo_expressions. 

INSERT demo_expressions 
       FROM TABLE @( VALUE #( ( id = 'X' num1 = 111 ) 
                              ( id = 'Y' num1 = 222 ) ) ).

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

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 was 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.

Example

Inserts all rows of the database table DEMO_JOIN1 in the table DEMO_JOIN3.

DELETE FROM demo_join1. 
INSERT demo_join1 
  FROM TABLE @( VALUE #( ( a = 'a1' b = 'b1' c = 'c1' d = 'd1' ) 
                         ( a = 'a2' b = 'b2' c = 'c2' d = 'd2' ) 
                         ( a = 'a3' b = 'b3' c = 'd3' d = 'd3' ) ) ). 

DELETE FROM demo_join3. 
INSERT demo_join3  FROM ( SELECT * FROM demo_join1 ). 

SELECT * 
       FROM demo_join3 
       INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).

Continue

INSERT dbtab - source