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

INSERT dbtab - source

Quick Reference

Syntax

...  @wa
  | { TABLE @itab [ACCEPTING DUPLICATE KEYS] }
  | ( SELECT subquery_clauses [UNION ...] ) ...

Alternatives:

1. ... @wa ...

2. ... TABLE @itab [ACCEPTING DUPLICATE KEYS] ...

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

Effect

A non-table-like data object wa can be specified as a data source after the additions VALUES and FROM of the statement INSERT. After FROM TABLE, an internal table itab can also be specified. Both data objects can be specified as host variables or host expressions. The contents of the row(s) to be inserted are taken from these data objects. The escape character @ should precede the work area 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 (as a host variable or host expression), from whose content a row is created for insertion in the database table. The work area must meet the prerequisites for use in Open SQL statements.

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

By default, an automatic client handling is performed, which means that any client identifier specified in wa is ignored and the current client is used instead. This is ignored by wa. Automatic client handling can be switched off using the addition CLIENT SPECIFIED.

Notes

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 [ACCEPTING DUPLICATE KEYS] ...


Effect

An internal table itab can be specified as a host variable or host expression after FROM and TABLE, from whose content multiple rows are created for insertion in the database table. The row type of the internal table must meet 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 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:

Notes

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 result sets of multiple subqueries. In this case, special rules query_clauses apply for specifying clauses.

If a subquery is used as a data source, automatic client handling in the INSERT statement cannot be deactivated 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.

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:

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:

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

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

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 dbtab - subquery_clauses
Example INSERT, FROM SELECT