Insert (single record):

Inset inserts a record into the data base table based on the key field if there is no match found in the data base. Other wise it ignores the record.

Syntax:
Insert <data base table> from <work area>.

Note: – SY-SUBRC is the system variable which contains zero with the above statement executed successfully otherwise it contains non zero. Most of the times it contains ‘4’.

Data wa_T001 like T001.
WA_T001-BUKRS = ‘ 3344’.
WA_T001-BUTXT = ‘BBSRTECH’.
WA_T001-ORT01 = ‘BBS’.
WA_T001-LAND1 = ‘IN’.
Insert T001 from WA_T001.
If SY-SUBRC = 0.
Write ‘Inserted’.
Else.
Write ‘Not inserted’.
Endif.

Insert (Multiple records): –
Insert inserts multiple records from internal table to data base table if there is no match found in the
data base for all the records of internal table based on the key field. If at least one record is matched
then it simply ignores the all records of internal table as well as terminates the entire transaction.

Syntax: –
Insert <data base table> from table <internal table>.

Data: WA_T001 LIKE T001,
IT_T001 LIKE TABLE OF WA_T001.
WA_T001-BUKRS = ‘0555’.
WA_T001-BUTXT = ‘GMR INFRA’.
WA_T001-ORT01 = ‘HYD’.
Append WA_T001 to IT_T001.
Clear WA_T001.
WA_T001-BUKRS = ‘0666’.
WA_T001-BUTXT = ‘GVK POWER’.
WA_T001-ORT01 = ‘CHE’.
Append WA_T001 to IT_T001.
Clear WA_T001.
WA_T001-BUKRS = ‘0777’.
WA_T001-BUTXT = ‘COAL INDIA’.
WA_T001-ORT01 = ‘BAN’.
Append WA_T001 to IT_T001.
Clear WA_T001.
Insert T001 from table IT_T001.

Here ‘0777’ company already exists in the data base. So it ignores all other records in internal table
as well as it terminates the entire transaction.
If you want avoid the termination of the program then you must place accepting duplicate keys in the
syntax of inserting.

Syntax:-
Insert <data base table> from table <internal table> accepting duplicate keys.
The above syntax avoids the termination of the program as well as inserts the non duplicate
records & ignores the duplicate records.

Note: – SY-DBCNT is the system variable which contains the number of records as
successfully processed into the data base.

Ex: – Insert T001 from table IT_T001 accepting duplicate keys.
Write SY-DBCNT.
Here 0555 & 0666 companies are inserted & 0777 is ignored.

Update (single record) / over write: –

Update updates a record into the data base table if there is a match found into the data base based on the key field. Otherwise it ignores the record.
Syntax: – Update <data base table> from <work area>.

Ex: –
Data WA_T001 like T001.
WA_T001-BUKRS = ‘0786’.
WA_T001-BUTXT = ‘BBSR Tech’.
WA_T001-ORT01 = ‘CTC’.
Update T001 from WA_T001.
If SY-SUBRC = 0.
Write ‘UPDATED’.
Else.
Write ‘NOT UPDATED’.
Endif.

Note: – whenever we are working with update then we must maintain change field information & also non change field information. Otherwise non change field information may be lost.

Update (Multiple records): –

This functionality is similar as update single record functionality.

Syntax:-
Update <data base table> from table <internal table>.:-
Update <data base table> from table <internal table>.

Update particular columns: –
Syntax:

Update <data base table> set <field 1> = <value 1>
<field 2> = <value 2> ….. where <condition>.

Ex: –

Update T001 set BUTXT = ‘BBSR Tech’
LAND1 = ‘IN’ where BUKRS = ‘0786’.
If SY-SUBRC = 0.
Write ‘updated’.
Else.
Write ‘not updated’.
Endif.

Modify:–

Modify acts like update if there is a match found in data base based on the key field otherwise it acts like insert. Modify never failed.

Syntax: –
Modify <data base table> from <work area>.
Modify <data base table> from table <internal table>.

Data: WA_T001 like T001,
IT_T001 like table of WA_T001.
WA_T001-BUKRS = ‘0555’.
WA_T001-BUTXT = ‘BBS TECH’.
WA_T001-ORT01 = ‘Swindown’.
Append WA_T001 to IT_T001.
WA_T001-BUKRS = ‘0777’.
WA_T001-BUTXT = ‘BATA PAK’.
WA_T001-ORT01 = ‘HYD’.
Append WA_T001 to IT_T001.
Modify T001 from table IT_T001.
If SY-SUBRC = 0.
Write ‘Modified’.
Else.
Write ‘not modified’.
Endif.

In this example ‘0555’ company already exist in the data base. So it acts like update or over write
& ‘0777’ company details aren’t available in data base. So it acts as insert.

Delete: –

Delete deletes the data from data base based on condition.

Syntax: –
Delete from <data base table> where <condition>.

Ex: –
Delete from T001 where BUKRS = ‘0555’.
If SY-SUBRC = 0.
Write ‘deleted’.
Else.
Write ‘not deleted’.
Endif.

DCL Commands for SAP Open SQL:

Commit work: –
This command is used to commit the data base changes.
Syntax:
commit work

Rollback work: –

This command is used to reverse the data base changes.

syntax:
Rollback work.

Through this tutorial you can learn how to learn Open SQL tutorial in SAP ABAP.

For complete ABAP tutorial please click here

To learn SAP ABAP from SAP please click here