Skip to content

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

INSERT dbtab - source

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


...  @wa|@( expr ) 
  | { TABLE @itab|@( expr ) [ACCEPTING DUPLICATE KEYS] } 

  | ( SELECT
subquery_clauses [UNION ...] ) ...

Alternatives

1. ... @wa|@( expr ) ...

2. ... TABLE @itab|@( expr ) [ACCEPTING DUPLICATE KEYS] ...

3. ... ( SELECT subquery_clauses [UNION ...] ) ...

Effect

A non-table-like data object can be specified as a host variable @wa or host expression @( expr ) as a data source after the additions VALUES and FROM of the statement INSERT. After FROM TABLE, an internal table or a subquery can be specified. The internal table can also be specified as a host variable @itab or host expression @( expr ). The content of the inserted row or rows is taken from these data objects or from the results set of the subquery.


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|@( expr ) ...

Effect

After VALUES and FROM, a non-table-like work area can be specified as a host variable @wa or as a host expression @( expr ), from whose content a row is created for insertion into the database table. The work area must meet the prerequisites for use in ABAP SQL statements.

  • When specifying a work area that does not contain any reference variables for LOB handles, the content of the row to insert is taken from the work area wa while ignoring its data type and from left to right in accordance with the structure of the database table or the view . No conversion is made to the ABAP type that is assigned to a column using its dictionary type.
  • 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. When the row is inserted, the content of the work area assigned to the individual columns of the database table is mapped by the database interface to its data types and converted if necessary. If content is not suitable, overflows or invalid values may raise exceptions.

If a view is specified in target that does not include all columns in the database table, these are set to the type-dependent 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.

Implicit ABAP SQL client handling applies, in which a client ID specified in wa is ignored. The current client is used by default. The addition USING CLIENT can be used to switch to an explicitly specified client. The addition CLIENT SPECIFIED can be used to switch to the client specified in wa.


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.
  • Exceptions due to invalid values can be raised in compatible work areas too. For example, components of the types d and t can contain invalid dates and times that are not accepted by columns of the types DATN and TIMN.
  • If a constructor expression is specified as a host expression for the work area wa, for which the data type is inferred using the # character, a structure consisting of all the database table columns is created as the type.
  • 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 INSERT implicitly.


Example

Inserts a new airline into the database table SCARR using a work area wa.

DATA scarr_wa TYPE scarr. 

scarr_wa = VALUE #( 
  carrid   = 'FF' 
  carrname = 'Funny Flyers' 
  currcode = 'EUR' 
  url      = 'http://www.funnyfly.com' ). 

INSERT INTO scarr VALUES @scarr_wa.

Example

Inserts a new airline into the database table SCARR with the value operator VALUE in a host expression.

INSERT INTO scarr VALUES @( VALUE #( 
  carrid   = 'FF' 
  carrname = 'Funny Flyers' 
  currcode = 'EUR' 
  url      = 'http://www.funnyfly.com' ) ).

Alternative 2

... TABLE @itab|@( expr ) [ACCEPTING DUPLICATE KEYS] ...

Effect

An internal table itab can be specified as a host variable @itab or host expression @( expr ) after FROM and TABLE, from whose content multiple rows are created for insertion into the database table. The row type of the internal table must meet the prerequisites for use in ABAP 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 writer streams can 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 the following options:

  • Using ACCEPTING DUPLICATE KEYS

    If the addition ACCEPTING DUPLICATE KEYS is specified, all rows are inserted for which this is possible. All rows that would produce duplicate entries with respect to the primary key or to a unique secondary index are discarded and sy-subrc set to 4. The system field sy-dbcnt is set to the number of rows that are inserted.
  • No use of ACCEPTING DUPLICATE KEYS
  • Handling of the exception CX_SY_OPEN_SQL_DB

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

    If the addition ACCEPTING DUPLICATE KEYS is not specified and if the exception is not handled, a runtime error occurs when a duplicate row is inserted. 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 the duplicate entry was made.


Notes

  • The addition ACCEPTING DUPLICATE KEYS does not indicate that duplicate key entries are accepted in the strict sense of the word. More specifically, no change is made to an existing entry as is the case when MODIFY is used. Instead, ACCEPTING DUPLICATE KEYS prevents the associated exception from being raised and sets the return code sy-subrc to 4.
  • If the runtime error produced by inserting existing rows is prevented by handling an exception, instead of by using the addition ACCEPTING DUPLICATE KEYS, then, if a database rollback is wanted, it must be initiated explicitly.
  • When an internal table is used, package by package processing causes only some of the rows being inserted to be visible to any reads running in parallel with the INSERT.
  • If a constructor expression as a host expression is specified as an internal table itab, for which the data type is inferred using the # character, a structured standard table (whose row type consists of all the database table columns) with an empty table key is created as the type.


Example

Inserts multiple rows in a host expression using the value operator VALUE. This example shows the two ways of dealing with duplicate rows.

TRY. 
    INSERT scarr FROM TABLE @( VALUE #( 
      ( carrid   = 'FF' 
        carrname = 'Funny Flyers' 
        currcode = 'EUR' 
        url      = 'http://www.funnyfly.com' ) 
      ( carrid   = 'XXL' 
        carrname = 'Extra Large Line' 
        currcode = 'USD' 
        url      = 'http://www.xxlline.com' ) ) ). 
  CATCH cx_sy_open_sql_db. 
    ... 
ENDTRY. 

INSERT scarr FROM TABLE @( VALUE #( 
  ( carrid   = 'FF' 
    carrname = 'Funny Flyers' 
    currcode = 'EUR' 
    url      = 'http://www.funnyfly.com' ) 
  ( carrid   = 'XXL' 
    carrname = 'Extra Large Line' 
    currcode = 'USD' 
    url      = 'http://www.xxlline.com' ) ) ) ACCEPTING DUPLICATE KEYS. 
IF sy-subrc = 4. 
  ... 
ENDIF.

Alternative 3

... ( SELECT subquery_clauses [UNION ...] ) ...

Effect

A parenthesized subquery can be specified as a data source after FROM. The lines of the result set of a subquery are inserted, which is defined by the corresponding clauses subquery_clauses. The language element UNION can be used to combine the results sets of multiple subqueries. In this case, special rules query_clauses apply for specifying clauses.

If a subquery is used as a data source, two cases must be distinguished in client handling of the INSERT statement:

  • If the subquery uses implicit client handling of the SELECT statement by default or switches it using the addition USING CLIENT, implicit client handling of the INSERT statement cannot be switched using the addition CLIENT SPECIFIED. The client column of a client-specific database table or classic view filled using the INSERT statement is filled with the ID of the current client or the client specified using USING CLIENT, regardless of the results set of the subquery.
  • If the subquery switches implicit client handling of the SELECT statement using one of the additions USING [ALL] CLIENTS [IN], implicit client handling of the INSERT statement must be switched using the addition CLIENT SPECIFIED. The results set of a subquery of this type can contain multiple client IDs and these are inserted in the client column of the target of the INSERT statement.

The data from the results set is inserted into the database table or classic view in question column by column in the database system. Columns are assigned using their position. The columns names in the result set are not important for assignment purposes. The columns assigned to each other must have the same type attributes with respect to built-in data type, length, and number of digits after the decimal point, with the following exceptions:

  • In the case of the numeric types INT1, INT2, INT4, and INT8, columns with a lesser value range can be assigned to a column with a greater value range.
  • In the case of the numeric type DEC, columns with shorter lengths can be assigned to columns with greater lengths. Furthermore, columns with fewer decimal places can be assigned to columns with more decimal places, as long as there are enough integer digits. The corresponding special types CURR and QUAN are handled here like DEC.
  • The numeric types DF16_DEC and DF34_DEC are handled like the numbers of type DEC (as they are saved) and the rule above applies with respect to lengths and decimal places.
  • In the case of the character-like type CHAR, columns with shorter lengths can be assigned to columns with greater lengths. The corresponding special types CLNT, LANG, CUKY, and UNIT are handled here like CHAR.

All other types must be exactly the same. This applies specifically to NUMC and RAW, where the lengths must match. The different categories of strings cannot be combined either.

The statement INSERT with subquery does not insert any null values into the database table or classic view in question. Null values for insertion can be produced in the following cases:

  • As results of outer joins in the FORM clause of the subquery
  • As results of SQL expressions in the SELECT list of the subquery
  • If a field read by the subquery already contains a null value

In these cases, the following is done instead of inserting a null value:

  • The type-dependent initial value is inserted for columns that are not key fields of the database table or classic view in question.
  • An exception that can be caught using the exception class CX_SY_OPEN_SQL_DB is raised in the case of columns that are key fields of the database table or classic view in question. If it is known statically that null values can be inserted into key fields, a syntax check warning that can be hidden by the pragma null_values is produced.

Columns of the database table or classic view in question that do not have a column in the results set of the subquery are also filled with their type-dependent initial value.

If it was possible to insert all rows of the results set, sy-subrc is set to 0. If it was not possible to insert a row from the results set (since a row with the same primary key or an identical unique secondary index exists), all previously inserted rows are discarded and a catchable exception of the class CX_SY_OPEN_SQL_DB is raised. If the results set of the subquery is empty, no row is inserted and sy-subrc is set to 4.

The statement INSERT with subquery cannot be used if logging is enabled for the table in question , and hence the corresponding technical attribute of the database table and the profile parameter rec/client are set accordingly . If used for a database table with logging enabled, the non-handleable exception DBSQL_DBPRT_STATEMENT is raised.


Notes

  • Using subqueries produces better performance than using standalone SELECT statements to read data into an internal table and using this table as a data source.
  • Unlike when inserting rows from an internal table itab, the content of the modified table or classic view is always defined after the exception CX_SY_OPEN_SQL_DB is handled.
  • This variant of the INSERT statement is particularly well suited to filling global temporary tables.
  • In certain circumstances, CAST expressions can be used in the SELECT list of the subquery for combinations between columns forbidden by the rules above.
  • A SELECT list of the subquery specified using * ignores all client columns, which means that client-specific CDS entities whose results sets do not have a client column can be accessed without any problems.
  • USING CLIENT can be used in the subquery to read the data of a client other than the client for which the data was written using INSERT. More specifically, the data from one client can be copied to a different client.
  • Attempts to fill key fields with null values raise exceptions, which also prevents duplicate entries from being made in the table or classic view in question.
  • Database tables and views accessed using this variant of the INSERT statement should not be expanded independently from one another, to avoid syntax errors.
  • To avoid exceptions when performing writes to a database table with logging enabled. the method IS_LOGGING_ON of the system class CL_DBI_UTILITIES can be used to branch to an alternative implementation.
  • When a subquery is used, the syntax check is performed in strict mode from Release 7.50, which handles the statement more strictly than the regular syntax check.
  • If USING CLIENT is used or a table or view is accessed that is filled by the INSERT statement in the subquery, the syntax check runs in strict mode from Release 7.53, which handles the statement more strictly than the regular syntax check. If used, USING [ALL] CLIENTS [IN] applies strict mode from Release 7.54.


Example

Inserts all rows of a join set of the database tables DEMO_JOIN1 and DEMO_JOIN2 into 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_join2. 
INSERT demo_join2 
  FROM TABLE @( VALUE #( ( d = 'd1' e = 'e1' f = 'f1' g = 'g1' ) 
                         ( d = 'd2' e = 'e2' f = 'f2' g = 'g2' ) 
                         ( d = 'd3' e = 'e3' f = 'f3' g = 'g3' ) ) ). 

DELETE FROM demo_join3. 
INSERT demo_join3  FROM ( SELECT a,b,c,d FROM demo_join1 
                            UNION 
                             SELECT d,e,f,g FROM demo_join2 ). 

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

cl_demo_output=>display( result ).

Executable Examples

Continue

INSERT, FROM SELECT