Use of insert/upsert in SQL Script.
This post uses SCARR table to show the use of INSERT/UPSERT in hana sql script.
-> INSERT can be used to create new record(s) with all columns or few columns including key columns.
-> UPSERT- Its INSERT or UPDATE . If the key values are already present then works as an update and if the key values not found then work as an INSERT. And can be used to update/insert desired columns or all columns.
The SCARR table has 5 fields.
Its a AMDP procedure , and the internal table contains 5 records and the INSERT with SELECT on the internal tables inserts 5 rows in the DB table.
This insert with select * , inserts all columns in the DB table.
The test report that calls amdp method.
Debug Details with internal table records- Do F8.
Now check the table and we have additional 5 records inserted.
For demo purpose, with help of debugger deleted the 5 records from DB table so that we can use the same data to show another use case.
Without using select all we can specify the field names also, the select field names should match the DB table field names. This way of writing provides little flexibility which will see in next.
Debug Details- F8.
Again it inserts the 5 records to the table.
Deleted records from the DB table-
Now this time need is to insert 3 records but not all columns, So in the INSERT statement the set of field names can be specified which to be inserted and similarly the follow on select should specify those fields that are provided in the INSERT field list.
Debug Details-
This time the insert works for 5 records but only for mentioned columns( 3 columns).
Deleted records from DB table-
Let’s see the use of UPSERT( its like MODIFY in ABAP open SQL).
The internal table LT_SCARR contains 5 records for which 3 records not present and other 2 records , the data is already present in the DB table.
Debug Details-
Now 3 rows inserted and for other 2 records are updated with 3 columns(carrname,currcode,url).
Deleted 3 inserted records from DB table:-
Now in UPSERT also, list of columns can be specified which to be updated/inserted.
Debug Details-
DB table Details- 3 records inserted with 3 columns and 2 records are updated with only one column(CARRNAME).
SAPCODES 