ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Read Accesses → SELECT

SELECT - INTO

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... { INTO 
      { {[CORRESPONDING FIELDS OF] wa}|(dobj1, dobj2, ...)} } 

  | { INTO|APPENDING 
         [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n] } 

  | { INTO @DATA(wa) } 
  | { INTO TABLE @DATA(itab) [PACKAGE SIZE n] } 

    [ creating ] ...  .

Alternatives

1. ... INTO [CORRESPONDING FIELDS OF] wa

2. ... INTO (dobj1, dobj2, ... )

3. ... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

4. ... INTO @DATA(wa)

5. ... INTO TABLE @DATA(itab) [PACKAGE SIZE n]

Effect

The information after INTO or APPENDING specifies to which data objects the results set of a SELECT statement or a FETCH statement is assigned. The following:

a single work area wa after INTO,
a list (in parentheses) of data objects dobj1, dobj2 ... after INTO,
an internal table itab after INTO or APPENDING

can be specified as a host variable with escape character @ The declaration operator DATA can be used to perform appropriate inline declarations.

When field symbols or dereferenced reference variables are specified for the work area, individual data objects, or internal tables in a SELECT loop closed using ENDSELECT, the data object that is the target of a field symbol or reference variable is identified precisely once, when the loop is entered. This data object is used as a target area in each loop pass. Any modifications to the assignment of a field symbol or reference variable within the loop are ignored.

If a LOB of the results set is associated with a LOB handle, it may be necessary to use creating to specify whether a data stream or a locator is being used.

Notes

When specifying the data objects, there are no dynamic variants that correspond to the other additions. Instead, dynamically created data objects can be used (see the example for CREATE DATA).
Whether data should better be read into an internal table or a work area depends on the type of further processing: If data is required only once in a program, it should be imported into a work area, row by row, by a SELECT. Reading data into an internal table requires more memory space (without the disadvantage) because of a considerably higher reading speed. If, on the other hand, data is required many times in a program, it should be read into an internal table. The disadvantage of the increased memory requirement is more than compensated for here by the advantage of a once-only selection.
If data is to be imported into an internal table, it is better to import it once into an internal table than to put it, row by row, into a work area and then finally add it to an internal table using APPEND.
If individual columns are being read and the structure of the results set is known statically, it is recommended that a list of data objects is used instead of using the addition CORRESPONDING FIELDS.
If the addition INTO CORRESPONDING is used and all the required information exists statically, the fields are assigned when the program is generated. Otherwise the fields are assigned once when the SELECT statement is executed, which causes a slight increase in the required runtime.
The addition INTO CORRESPONDING affects the results set defined in the SELECT list. If one or more names match, all the columns for which there are no name matches are removed from the results set. If there are no name matches, none of the columns are removed from the results set.
The variants with the addition INTO CORRESPONDING FIELDS run the risk of having target areas with more components than are actually filled. This should only be the case if the components that are not filled are then filled in the program. Otherwise appropriately sized work areas should be used to avoid a large amount of memory being filled by initial values (especially in internal tables).
The statement SELECT also has an obsolete short form, in which INTO or APPENDING can be omitted.
Reference variables for LOB handles or LOB handle structures cannot currently be specified after the statement FETCH.
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

... INTO [CORRESPONDING FIELDS OF] wa

Effect

For wa, a data object can be specified as a host variable using the escape character @. This must meet certain prerequisites without specifying CORRESPONDING FIELDS OF. If the results set consists of a single row, this row is assigned to wa. If the results set has multiple rows, SELECT must be followed by an ENDSELECT statement; the result set is assigned to the work area wa row-by-row and can be evaluated in the loop. After ENDSELECT, the work area wa contains the row that was assigned last. If used in the FETCH statement, a row is extracted at the current cursor position. If the result is empty, the work area remains unchanged.

The rows of the results set are assigned as follows, based on the columns specified after SELECT:

* specified without the addition CORRESPONDING FIELDS

If all columns are read using * and CORRESPONDING FIELDS is not specified, SELECT behaves as follows:

When specifying a work area that does not contain any reference variables for LOB handles, the row is assigned to the result set left-aligned and unconverted in accordance with the structure of the results set. Unaffected parts of wa retain their previous content. To be able to access the components of the result sets according to type, the work area wa must be structured like the result set.

When a LOB handle structure is specified, it must be structured exactly like the structure of the database table or the view (and the prerequisites must be met). The contents of the columns of the results set that are not assigned to any LOB handle components are directly assigned to the corresponding components of the work area. One LOB handle is created for each LOB handle component.

All other combinations

If the results set consists of a single column specified explicitly after SELECT or a single SQL expression or a single aggregate expression, wa can be an elementary data object or a structure. If the results set consists of multiple columns, it must be a structure and the following rules apply:

If the addition CORRESPONDING FIELDS is not specified, wa must contain enough components and the contents of the columns are assigned to the components of wa from left to right in the order specified after SELECT.

If the addition CORRESPONDING FIELDS is specified, only those contents of columns for which there are identically named components in wa are assigned to them. This respects the alternative column names. Columns and aggregate expressions that appear multiple times can only be assigned using alternative column names, if CORRESPONDING FIELDS is specified. If a column name appears multiple times and no alternative column name was specified, the last column listed is assigned. If one or more names match, all the columns for which there are no name matches are removed from the results set defined in the SELECT list. If there are no name matches, none of the columns are removed from the results set.

Assignment rules apply to the individual assignments. If a LOB of a reference variable for LOB handle is assigned, a LOB handle is created.

If CORRESPONDING FIELDS is used, the columns of the results set defined in the SELECT list correspond to the components of the target area. Among other things, there must be at least one match or there must be an identically named component in the target area for each explicitly specified column.

Notes

The second point above, "All Other Combinations" also includes data_source~* when specified in the SELECT list. In this case, wa must be a structure. This is particularly valid when only one database table or view data_source~* is specified. If a single data_source~* is specified, therefore, other rules apply than in the case where * is specified, even if the same results set are defined both times.
The addition CORRESPONDING FIELDS ignores names which were only defined using the addition AS name of the statement INCLUDE or when integrating the structures in ABAP Dictionary. However, components that were renamed using the addition RENAMING WITH SUFFIX of the statement INCLUDE or similarly in ABAP Dictionary are not ignored.
If the addition CORRESPONDING FIELDS is used, fields with matching names (if statically identifiable) are assigned during program generation or are otherwise assigned once when the SELECT statement is executed.

Example

In this example, four columns of the results set are read into four correspondingly named components of a work area.

DATA wa TYPE spfli. 

SELECT carrid, connid, cityfrom, cityto 
       FROM spfli 
       INTO CORRESPONDING FIELDS OF @wa. 
  cl_demo_output=>write_data( wa ). 
ENDSELECT. 
cl_demo_output=>display( ).

Alternative 2

... INTO (dobj1, dobj2, ... )

Effect

If the results set consists of multiple columns or aggregate expressions specified explicitly in the SELECT list, a list of elementary data objects dobj1, dobj2, ... (in parentheses and separated by commas) can be specified as host variables prefixed by the escape character @ after INTO. The same number of elementary data objects dobj must be specified as there are columns in the results set.

The content of the columns in the result sets is assigned to the data objects from left to right, according to the order specified after SELECT. Assignment rules apply to the individual assignments. If a LOB of a reference variable for LOB handle is assigned, a LOB handle is created. If the result sets is empty, the data objects remain unchanged.

If the results set consists of one row, the columns are assigned from that row. If the results set contains multiple rows, SELECT must be followed by an ENDSELECT statement; the columns of the result set are assigned to the data objects row-by-row and they can be evaluated in a loop. If used in the statement FETCH, the columns of the row are extracted at the current cursor position.

Note

No list can be specified after INTO if the results set is defined by specifying or dbtab1~, dbtab2~*, ... in the SELECT list.

Example

In this example, four columns of the results set are read into four individually specified columns of a structure. Unlike in the previous example, the runtime environment does not compare names here.

DATA wa TYPE spfli. 

SELECT carrid, connid, cityfrom, cityto 
       FROM spfli 
       INTO (@wa-carrid, @wa-connid, @wa-cityfrom, @wa-cityto). 
  cl_demo_output=>write_data( wa ). 
ENDSELECT. 
cl_demo_output=>display( ).

Alternative 3

... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

Addition

... PACKAGE SIZE n

Effect

If the results set consists of multiple rows, an internal table itab of any table type can be specified as a host variable using the escape character @ after INTO or APPENDING. The row type of the internal table must meet the prerequisites.

The results set is inserted into the internal table itab row-by-row; a sorting process is executed in the case of a sorted table. If INTO is used, the internal table is initialized. Previous rows remain intact if APPENDING is used.

Before any assignment of a row of the result sets, an initial row of the internal table itab is created and the row of the result set is assigned to this row. When assigning a row of the results set to a row of the internal table with or without CORRESPONDING FIELDS, the same rules apply as when assigning to an individual work area wa (see above) with the exception that when inserting into internal tables, LOB handles can be created as locators but not as reader streams.

If the addition PACKAGE SIZE is not used, all rows of the results set are inserted in the internal table itab and the ENDSELECT statement must not be specified after SELECT.

If the result sets is empty, the internal table is initialized when INTO is used, and remains unchanged when APPENDING is used.

Notes

When specifying an internal table with a unique primary or secondary table key, an exception is raised that cannot be handled if an attempt is made to create a duplicate entry.
If the internal table contains more columns than required when using the addition CORRESPONDING FIELDS, generally too much memory is being used by initial fields and a warning is displayed by the syntax check. In cases where the columns are needed, for example when they are filled with derived values by the program, a pragma can be used to suppress the warning.
Since the maximum number of data streams that can be assigned to an Open SQL statement is limited to 16, it does not make any sense to create reader streams when importing internal tables.

Addition

... PACKAGE SIZE n

Effect

If the addition PACKAGE SIZE is specified, all rows of the results set for SELECT are processed in a loop, which must be closed with ENDSELECT. They are inserted in packages of n rows in the internal table itab. A host variable or a literal of type i (which contains the number of rows) is expected for n. The content of n must match the data type i in accordance with the rules for a lossless assignment. If the value of n is less than 0, an exception is raised that cannot be handled. If n is equal to 0, all rows of the results set are inserted in the internal table itab. If used in the statement FETCH, n rows are extracted from the current cursor position.

If INTO is used, the internal table is initialized before each insertion and, in the SELECT loop, it only contains the rows of the current package. If APPENDING is used, a further package is added to the existing rows of the internal table for each SELECT loop or for each extraction using FETCH.

After ENDSELECT, the content of itab is not defined if INTO is used. That is, the table can either contain the rows of the last package or it can be initial. If APPENDING is used, the content of itab retains the state of the last loop pass.

Notes

The addition PACKAGE SIZE can be used after INTO to limit the amount of data read in one go. Otherwise a runtime error can occur when reading a data set that is too large into an internal table so that its maximum size is exceeded. PACKAGE SIZE cannot prevent this runtime error after APPENDING.
If the addition PACKAGE SIZE is specified with the addition FOR ALL ENTRIES, all selected rows are read initially into an internal system table and the packages are only created when they are passed from the system table to the actual target table. The addition FOR ALL ENTRIES thereby negates the effect of PACKAGE SIZE in preventing memory overflow.
The addition PACKAGE SIZE does not influence the size of the packages (configured in the profile parameters) used to transport data between the database server and the application server.

Example

In this example, all columns of a results set are read into an internal table, the row type of which is a nested structure with the same structure as the results set. Note that in practice, the column carrid exists twice in the results set with the same content and, after the assignment, this content is stored redundantly in the columns struc1-carrid and struc2-carrid of the internal table.

DATA: BEGIN OF wa, 
        struc1 TYPE scarr, 
        struc2 TYPE spfli, 
      END OF wa. 

DATA itab LIKE SORTED TABLE OF wa 
          WITH UNIQUE KEY table_line. 

DATA: BEGIN OF output_wa, 
        carrid LIKE wa-struc1-carrid, 
        carrname LIKE wa-struc1-carrname, 
        connid LIKE wa-struc2-connid, 
      END OF output_wa, 
      output LIKE TABLE OF output_wa WITH EMPTY KEY. 

SELECT * 
       FROM scarr 
         INNER JOIN spfli ON scarr~carrid = spfli~carrid 
       INTO TABLE @itab. 

LOOP AT itab INTO wa. 
  output_wa-carrid = wa-struc1-carrid. 
  output_wa-carrname = wa-struc1-carrname. 
  output_wa-connid = wa-struc2-connid. 
  APPEND output_wa TO output. 
ENDLOOP. 

cl_demo_output=>display_data( output ).

Alternative 4

... INTO @DATA(wa)

Alternative 5

... INTO TABLE @DATA(itab) [PACKAGE SIZE n]

Effect

Inline declaration of the target area. The declaration operator DATA must be prefixed with the escape character @. The data type of the new data object is constructed in accordance with the structure of the results set defined after SELECT and the number of database tables or views specified after FROM. INTO @DATA(wa) declares a flat data object wa of this type; INTO TABLE @DATA(itab) declares a standard table itab of this row type with an empty table key. The same applies to PACKAGE SIZE as when specifying an existing internal table.

The prerequisites for an online declaration are as follows:

The SELECT list must be specified statically.
The results set defined in the SELECT list cannot have multiple columns with the same name. This can be bypassed using alias names.
In a multi-column results set, each SQL expression and each aggregate expression must have an alias name.

The data type of the declared data object wa or itab is determined as follows:

If the results set in the SELECT list is defined using a single specified column col_spec for which no name can be identified, the data type of wa or the row type of itab is its elementary type.
If the results set in the SELECT list is defined using a single specified column col_spec for which no name can be identified, the data type of wa or the row type of itab is a structure with a component, with its elementary type.
If the results set in the SELECT list is defined using a single data_source~* or a list of multiple specified columns col_spec, the data type of wa or the row type of itab is a structure with elementary components. The data types of the components are the elementary types of the columns in the results set in the order defined there.
If the results set in the SELECT list is defined using data_source~*, the data type of wa or the row type of itab is a nested structure. There is a substructure with the name or alias name of the table or view for every individual table or view data_source specified. The data types of the components of the substructures are the elementary types of the database tables or views in the order defined there.
If the results set in the SELECT list is defined using *, the data type depends on the number of database tables or views specified after FROM:

In reads from a database table dbtab, view view or cds_view, the data type of wa or the row type of itab is the same as in a definition of the results set using data_source~* (see above).

In reads from multiple database tables or views data_source1, data_source2, ... using a join, the data type of wa or the row type of itab is the same as in a definition of the results set using data_source1~*, data_source2~*, ... (see above).

The elementary data type of an elementary data object or an elementary component of a structure is constructed as follows:

For columns of database tables or views, the data type is taken from ABAP Dictionary.
For SQL expressions and aggregate expressions, the data type is their result type.
For a single host variable as an SQL expression, the data type is its ABAP type.

The names of the elementary components of a structure match the names of the associated columns from the results set. Any alias names defined there are respected.

Notes

Specified columns for which no name can be identified are SQL expressions and aggregate expressions without alias name.
When an inline declaration @DATA(itab) is specified after APPENDING TABLE, this addition usually works like INTO TABLE and is therefore pointless in this combination.
No inline declarations can be made after FETCH.
When inline declarations are used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

Continue

SELECT - CREATING

SELECT - Assignment Rules

SELECT - LOB Handles