SAP® Documentation

Single view

ABAPINSERT_SOURCE - INSERT SOURCE

General Data in Customer Master   CL_GUI_FRONTEND_SERVICES - Frontend Services  
This documentation is copyright by SAP AG.
SAP E-Book

INSERT dbtab - source

Short 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 or a subquery can also be specified. The data objects wa and itab can be specified as host variables or host expressions. The escape character @ should precede the work area name or the internal table name (as should be the case with every host variable). 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 ...


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

  • When specifying a work area that does not contain any reference variables for LOB Handles, the content of the row to be added is taken from the work area wa while ignoring its data type and without converting it from left to right in accordance with the structure of the database table or the view .
  • 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.

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

  • 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.
  • 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 areadbtab 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 $[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 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.

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, 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 (regardless of the results set of the subquery) with the ID of the current client
or of the client specified using USING CLIENT.

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 , 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 , which handles the statement more strictly than the regular syntax check.


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






CL_GUI_FRONTEND_SERVICES - Frontend Services   Fill RESBD Structure from EBP Component Structure  
This documentation is copyright by SAP AG.

Length: 29791 Date: 20191216 Time: 143545     sap01-206 ( 92 ms )

Our Service

Looking for Support? Questions?

The

Consolut

Callback-Service

Leave us your contact details and we will call you back. Panels marked with * are mandatory.