Kontakt

Ansicht
Dokumentation

ABAPINSERT_SOURCE - INSERT SOURCE

ABAPINSERT_SOURCE - INSERT SOURCE

Fill RESBD Structure from EBP Component Structure   TXBHW - Original Tax Base Amount in Local Currency  
This documentation is copyright by SAP AG.
SAP E-Book

INSERT dbtab, source

Short Reference



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

Alternatives:

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

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

3. ... ( SELECT subquery_clauses $[UNION$|INTERSECT$|EXCEPT ...$] ) ...

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 row or rows to be inserted is taken from these data objects or from the result 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 .

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 DDIC 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 inserted is taken from the work area wa while ignoring its data type and from left to right in accordance with the structure of the DDIC 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 exactly like the structure of the DDIC database table, in accordance with the prerequisites. 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 the 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 into the DDIC 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 DDIC 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 DDIC 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 DDIC database table in question, the attribute NOT NULL is not selected in the database.

Notes

  • The work area wa should always be declared in relation to the DDIC database table or the DDIC view in the 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. 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 the DDIC database table or the DDIC 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 if a table work area dbtab for the corresponding DDIC database table or the DDIC view is declared using the statement TABLES. The runtime framework then adds the addition FROM dbtab to the statement INSERT implicitly.

Example

Insertion of a new airline into the DDIC database table SCARR using a work area wa.

Example

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

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 DDIC 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 assembled into a row to be inserted 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 DDIC 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 DDIC 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:

  • Use of 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 catchable 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 undoes 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 using the addition ACCEPTING DUPLICATE KEYS, then, if a database rollback is required, it must be initiated explicitly.
  • When an internal table is used, package by package processing causes only some of the rows to be inserted to be visible to any reads running in parallel with the INSERT.

Example

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

Alternative 3

... ( SELECT subquery_clauses $[UNION$|INTERSECT$|EXCEPT ...$] ) ...


Effect

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

The statement INSERT with subquery cannot be used if logging is enabled for the table that to be filled. If used for a DDIC database table with logging enabled, the uncatchable exception DBSQL_DBPRT_STATEMENT is raised. The corresponding warning from the syntax check can be switched off with the pragma ##logging_versus_from_select. Logging is enabled for a DDIC database table if the corresponding technical attribute of the DDIC database table and the profile parameter rec/client are set accordingly.

If a subquery is used as a data source, two cases must be considered 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-dependent DDIC database table or DDIC table 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 result 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 result set of a subquery of this type can contain multiple client IDs and these are inserted into the client column of the target of the INSERT statement.

The data from the result set is inserted into the DDIC database table or DDIC view to be filled 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 decimal places, with the following exceptions:

  • In the case of the numeric types INT1, INT2, INT4, and INT8, columns with a smaller 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 stored 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 null values into the DDIC database table or DDIC view to be filled. Null values to be inserted can be produced in the following cases:

  • As results of outer joins in the FORM clause 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 DDIC database table or DDIC view to be filled.
  • A catchable exception of exception class CX_SY_OPEN_SQL_DB is raised in the case of columns that are key fields of the DDIC database table or DDIC view to be filled. 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 DDIC database table or DDIC view to be filled that do not have a column in the result set of the subquery are also filled with their type-dependent initial value.

If it was possible to insert all rows of the result set, sy-subrc is set to 0. If it was not possible to insert a row from the result 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 result set of the subquery is empty, no row is inserted and sy-subrc is set to 4.

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 DDIC view is always defined after the exception CX_SY_OPEN_SQL_DB is handled.
  • 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-dependent CDS entities whose result 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 DDIC view to be filled.
  • DDIC database tables and DDIC views accessed using this variant of the INSERT statement should not be expanded independently of one another, to avoid syntax errors.
  • To avoid exceptions when performing writes to a DDIC 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 is performed in strict mode from Release , which handles the statement more strictly than the regular syntax check. If used, USING $[ALL$] CLIENTS $[IN$] leads to strict mode from Release .

Example

Insertion of all rows of a union set of the DDIC database tables DEMO_JOIN1 and DEMO_JOIN2 into the table DEMO_JOIN3. Only if logging is disabled for DEMO_JOIN3, a subquery can be used. Otherwise, the data must be inserted into an internal table using selection.






Fill RESBD Structure from EBP Component Structure   PERFORM Short Reference  
This documentation is copyright by SAP AG.

Length: 25627 Date: 20240426 Time: 092404     sap01-206 ( 385 ms )