In Chapter 5, you learned how SAP defines tables. The Data Dictionary holds the definitions — the shapes, the fields, the types. But a definition alone is just an empty container. What makes a table useful is the data inside it.

This chapter is where you learn how to put data into tables, take it out, change it, and remove it. That is done with Open SQL — ABAP’s database access language. It is one of the most important skills in the entire ABAP ecosystem, and it appears in nearly every interview for junior and mid-level roles.

You will learn the four fundamental statements — SELECT, INSERT, UPDATE, DELETE — plus JOINs, WHERE clauses, aggregate functions, and the critical role of sy-subrc after every database operation.

🎯 What You Will Learn in This Chapter

• What Open SQL is and how it differs from ABAP SQL in Cloud
• The SELECT statement in all its forms — single, list, aggregate
• The WHERE clause and how to filter data
• How to use JOIN to read from multiple tables
• INSERT, UPDATE, and DELETE for modifying data
• How sy-subrc tells you whether each operation succeeded
• Best practices for writing safe, performant database code

🧭 Which Path Should I Choose?

Before writing code, decide which path applies to you. This chapter has two tracks.

What You Can Do With Each Path

📘 What is Open SQL?

Open SQL is ABAP’s own subset of SQL. It looks like SQL, but it is not standard SQL. It is database-independent — the same Open SQL statement works on SAP HANA, Oracle, SQL Server, and every other database SAP supports, because SAP translates it into the specific database’s syntax behind the scenes.

Open SQL has four fundamental operations, often called CRUD:

Every database operation in ABAP falls into one of these four categories. Master these and you can do anything with data in SAP.

🔍 SELECT — Reading Data

The SELECT statement retrieves data from one or more tables. It is the most-used statement in ABAP, and it comes in several forms.

Form 1: Single Record

SELECT SINGLE * FROM zcustomer
  INTO @DATA(ls_customer)
  WHERE customer_id = @lv_id.

IF sy-subrc = 0.
  cl_demo_output=>write( |Found: { ls_customer-customer_name }| ).
ELSE.
  cl_demo_output=>write( 'No customer found.' ).
ENDIF.

SELECT SINGLE returns exactly one record (or none). Use it when you know the key or the WHERE clause will match a single row.

Form 2: Multiple Records into an Internal Table

DATA lt_customers TYPE TABLE OF zcustomer.

SELECT * FROM zcustomer
  INTO TABLE @lt_customers
  WHERE city = @lv_city.

IF sy-subrc = 0.
  cl_demo_output=>write( |Found { lines( lt_customers ) } customers in { lv_city }| ).
ENDIF.

INTO TABLE reads multiple rows into an internal table (which you will learn deeply in Chapter 7). The lines( ) function returns the number of rows.

Form 3: Specific Fields Only

SELECT customer_id, customer_name, city
  FROM zcustomer
  INTO TABLE @DATA(lt_summary)
  WHERE join_date >= @lv_from_date.

Reading only the fields you need is faster than SELECT * because less data travels from the database to your program. Always list only the fields you actually use.

SELECT * FROM zi_customer
  INTO TABLE @DATA(lt_customers).

SELECT * FROM zcustomer
  INTO TABLE lt_customers.

🎯 WHERE — Filtering Your Data

The WHERE clause controls which rows the SELECT returns. Without it, you get everything in the table.

Common operators

Example: Combining Conditions

SELECT * FROM zcustomer
  INTO TABLE @DATA(lt_filtered)
  WHERE city = @lv_city
    AND age > 25
    AND join_date >= @lv_from_date.

📊 Aggregate Functions

ABAP SQL has five aggregate functions that perform calculations across rows:

Example: Count and Sum

SELECT COUNT( * ), SUM( salary )
  FROM zcustomer
  INTO @DATA(ls_stats)
  WHERE city = @lv_city.

cl_demo_output=>write( |Total customers: { ls_stats-count }| ).
cl_demo_output=>write( |Total salary: { ls_stats-sum }| ).

Example: Grouped Aggregates

SELECT city, COUNT( * ) AS customer_count
  FROM zcustomer
  INTO TABLE @DATA(lt_by_city)
  GROUP BY city.

LOOP AT lt_by_city INTO DATA(ls_row).
  cl_demo_output=>write( |{ ls_row-city }: { ls_row-customer_count }| ).
ENDLOOP.

GROUP BY collapses rows that share the same value in the specified column, which is perfect for reports that summarise data.

🔗 JOINs — Reading from Multiple Tables

Real SAP data is spread across many tables. To combine them, you use a JOIN. The most common type is INNER JOIN, which returns only rows that match in both tables.

Example: Customer + Orders

SELECT c~customer_id, c~customer_name, o~order_id, o~order_total
  FROM zcustomer AS c
  INNER JOIN zorders AS o
    ON c~customer_id = o~customer_id
  INTO TABLE @DATA(lt_result)
  WHERE c~city = @lv_city.

Notes:

• Table aliases (c and o) make the query readable
• The tilde (~) separates alias from field name: c~customer_id
• The ON clause specifies which fields must match

Join types

➕ INSERT — Adding New Records

To create new data, use INSERT. You will use it less often than SELECT, but it is essential for any data-entry program.

Insert a single record

DATA ls_new TYPE zcustomer.
ls_new-customer_id   = 'C001'.
ls_new-customer_name = 'Rahul Sharma'.
ls_new-city          = 'Pune'.
ls_new-join_date     = sy-datum.

INSERT zcustomer FROM @ls_new.

IF sy-subrc = 0.
  cl_demo_output=>write( 'Customer inserted successfully.' ).
ELSE.
  cl_demo_output=>write( 'Insert failed.' ).
ENDIF.

Insert multiple records

DATA lt_new_customers TYPE TABLE OF zcustomer.

" Fill lt_new_customers with data ...

INSERT zcustomer FROM TABLE @lt_new_customers.

✏️ UPDATE — Modifying Existing Records

To change data, use UPDATE. You can update specific fields or entire records.

Update specific fields

UPDATE zcustomer
  SET city = @lv_new_city
  WHERE customer_id = @lv_id.

IF sy-subrc = 0.
  cl_demo_output=>write( 'Customer updated.' ).
ENDIF.

Update from a structure

DATA ls_update TYPE zcustomer.
ls_update-customer_id   = 'C001'.
ls_update-customer_name = 'Rahul Sharma (updated)'.
ls_update-city          = 'Mumbai'.

UPDATE zcustomer FROM @ls_update.

🗑️ DELETE — Removing Records

To remove data, use DELETE. Just like UPDATE, always use a WHERE clause unless you truly want to clear the whole table.

DELETE FROM zcustomer
  WHERE customer_id = @lv_id.

IF sy-subrc = 0.
  cl_demo_output=>write( 'Customer deleted.' ).
ELSE.
  cl_demo_output=>write( 'No customer matched the ID.' ).
ENDIF.

⚠️ sy-subrc — The Critical Check

Every database operation updates the system field sy-subrc. It tells you whether the operation succeeded. Ignoring it is the most common mistake in junior ABAP code.

The pattern is always the same:

SELECT SINGLE * FROM zcustomer
  INTO @DATA(ls_customer)
  WHERE customer_id = @lv_id.

IF sy-subrc = 0.
  " success path
ELSE.
  " error handling
ENDIF.

🛠 Full Worked Example

Let’s put everything together into a working program. This example inserts, reads, updates, and deletes — with proper sy-subrc checks at each step.

🔵 Paid Path (Classic ABAP in SE38)

REPORT zsql_ch6_demo.

DATA: ls_customer TYPE zcustomer,
      lt_results  TYPE TABLE OF zcustomer,
      lv_id       TYPE zcustomer-customer_id VALUE 'C100'.

" 1. INSERT a new customer
ls_customer-customer_id   = lv_id.
ls_customer-customer_name = 'Test Customer'.
ls_customer-city          = 'Pune'.
ls_customer-join_date     = sy-datum.

INSERT zcustomer FROM ls_customer.
IF sy-subrc = 0.
  WRITE: / 'Step 1: INSERT succeeded.'.
ELSE.
  WRITE: / 'Step 1: INSERT failed (may already exist).'.
ENDIF.

" 2. SELECT the record back
SELECT SINGLE * FROM zcustomer
  INTO ls_customer
  WHERE customer_id = lv_id.

IF sy-subrc = 0.
  WRITE: / 'Step 2: Found', ls_customer-customer_name.
ELSE.
  WRITE: / 'Step 2: No record found.'.
ENDIF.

" 3. UPDATE the record
UPDATE zcustomer
  SET city = 'Mumbai'
  WHERE customer_id = lv_id.

IF sy-subrc = 0.
  WRITE: / 'Step 3: UPDATE succeeded.'.
ENDIF.

" 4. SELECT all records in Mumbai
SELECT * FROM zcustomer
  INTO TABLE lt_results
  WHERE city = 'Mumbai'.

WRITE: / 'Step 4: Records in Mumbai:', lines( lt_results ).

" 5. DELETE the record
DELETE FROM zcustomer WHERE customer_id = lv_id.

IF sy-subrc = 0.
  WRITE: / 'Step 5: DELETE succeeded.'.
ENDIF.

🟢 Free Path (ABAP Cloud in Eclipse ADT)

In Cloud, direct INSERT/UPDATE/DELETE on transparent tables is not permitted. You would use a RAP Business Object (Chapter 17) to modify data, or a released CDS View to read it. The syntax you learned above is almost identical, but the safety model is different:

METHOD run.
  " Reading from a CDS View (only allowed way in Cloud)
  SELECT * FROM zi_customer
    INTO TABLE @DATA(lt_customers)
    WHERE city = @lv_city.

  IF sy-subrc = 0.
    cl_demo_output=>write( |Found { lines( lt_customers ) } customers.| ).
  ELSE.
    cl_demo_output=>write( 'No customers found.' ).
  ENDIF.

  cl_demo_output=>display( ).
ENDMETHOD.

🧠 What Just Happened Under the Hood

Understanding how ABAP talks to the database helps you write faster code and debug more effectively.

This is why checking sy-subrc matters — it is the only reliable signal that the operation completed successfully on the database side.

📝 Chapter Summary

• Open SQL is ABAP’s database-independent SQL subset. The same statement works on every database SAP supports.
• The four fundamental operations are SELECT, INSERT, UPDATE, DELETE (CRUD).
• SELECT SINGLE returns one record. SELECT ... INTO TABLE returns many records into an internal table.
• The WHERE clause filters rows. Combine conditions with AND, OR, IN, BETWEEN, and LIKE.
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarize data. Use GROUP BY for category summaries.
• INNER JOIN combines tables; LEFT OUTER JOIN keeps unmatched rows from the left table.
• In modern ABAP SQL, every ABAP variable inside a statement must be prefixed with @.
• Always check sy-subrc after every SELECT, INSERT, UPDATE, and DELETE.
• Never write UPDATE or DELETE without a WHERE clause unless you truly want to modify every row.
• In ABAP Cloud, direct modification of transparent tables is restricted — you use RAP business objects instead.