Deleting from temporary table in SAP HANA

The temporary tables in SAP HANA are session specific, which means they exist only within the current database session. For deleting data from temporary tables, you have two main options depending on your SAP HANA release version.

Using TRUNCATE Command

The preferred method for clearing all data from a temporary table is using the TRUNCATE command ?

TRUNCATE TABLE #temptable;

The TRUNCATE command removes all rows from the temporary table quickly and efficiently, as it deallocates the data pages instead of deleting rows one by one.

Using DELETE Command

In recent SAP HANA releases, the DELETE command also works fine with temporary tables. Here is a complete example demonstrating the delete operation ?

DROP TABLE #temptable;
CREATE LOCAL TEMPORARY TABLE #temptable(id INTEGER, str NVARCHAR(30));
INSERT INTO #temptable VALUES (1, 'abc');
INSERT INTO #temptable VALUES (2, 'xyz');
INSERT INTO #temptable VALUES (3, 'def');
SELECT * FROM #temptable;
DELETE FROM #temptable;
SELECT * FROM #temptable;

The output of the first SELECT statement shows ?

ID    STR
1     abc
2     xyz
3     def

After the DELETE operation, the second SELECT statement returns ?

(0 rows)

Key Differences

TRUNCATE is faster for removing all data as it deallocates entire data pages, while DELETE provides more flexibility for conditional data removal using WHERE clauses. Both commands work effectively with temporary tables in modern SAP HANA versions.

Conclusion

Use TRUNCATE for quickly clearing all data from temporary tables, or DELETE for more granular control over which rows to remove, especially in recent SAP HANA releases where both operations are fully supported.