The Open SQL INSERT statement used for inserting data into a database table. INSERT statement can insert single or multiple rows in a single execution.
Inserting data to table can perform using the table work area variable that is having exactly same structure as table.
INSERT statement allows to insert one or more rows into the database table. We can only insert row into an ABAP Dictionary view if it is created on one table, and its maintenance status is defined as Read and change.
We can specify the database table either statically or dynamically. Inserting data to table can perform in two ways and those are –
- Inserting single row using table work area
- Inserting multiple rows using internal table
Inserting single row using table work area –
To insert a single row into a database table, use the following syntax –
INSERT db-table FROM work-area.
db-table specifies the name of a ABAP Dictionary database table and work-area is the table work area.
The work area work-area data written to the database table db-table. The work area work-area should have the same type, length and alignment as the database table structure. The data is placed in the database table according to the table structure, and regardless of the work area structure. It is a good idea to define the work area with reference to the database table structure.
If the database table does not already have a row with the same key as specified in the work area, the operation completed successfully and SY-SUBRC is set to 0. Otherwise, the line is not inserted, and SY-SUBRC is set to 4.
Inserting several rows using internal table –
To insert several rows into a database table, use the following syntax –
INSERT db-table FROM TABLE internal-table [ACCEPTING DUPLICATE KEYS].
db-table specifies the name of a target database table and internal-table is the name of the source internal table.
The above syntax writes all rows of the internal table internal-table to the database table in one single operation.
If the system is able to insert all of the lines from the internal table internal-table, SY-SUBRC is set to 0. If one or more lines cannot be inserted because the database already contains a row with the same key, a runtime error occurs. We can prevent the runtime error occurring by using the addition ACCEPTING DUPLICATE KEYS. In this case, the rows that would cause runtime errors are discarded, and SY-SUBRC is set to 4.
The system field SY-DBCNT contains the number of rows inserted into the database table, regardless of the value in SY-SUBRC.
If we want to insert more than one line into a database table, it is more efficient way to insert the row one by one.
Example –
The below example to insert the below IFB product information into the table ZTC_TPRODUCT.
| PRODUCTID | PRODUCT | PRODUCT PRICE |
|---|---|---|
| IFB3 | IFB FRIDGE DOUBLE DOOR | 49000 |
Code –
*&---------------------------------------------------------------------*
*& Report Z_OPENSQL
*&---------------------------------------------------------------------*
*& Written by TutorialsCampus
*&---------------------------------------------------------------------*
REPORT Z_OPENSQL.
* Specifying table name
TABLES ZTC_TPRODUCT.
DATA: WA LIKE ZTC_TPRODUCT.
* New record information to the table
WA-PRODUCTID = 'IFB3'.
WA-PRODUCT = 'IFB FRIDGE DOUBLE DOOR'.
WA-PRODUCT_PRICE = 49000.
* Inserting data into the table from work area
INSERT ZTC_TPRODUCT FROM WA.
* Result display and error handling
IF SY-SUBRC 0.
WRITE: 'Data not inserted into the table ZTC_TPRODUCT,
Return code is: ', SY-SUBRC.
ELSE.
WRITE 'Data inserted into the table ZTC_TPRODUCT...'.
ENDIF.
Output –
Table Data After INSERT –