how to delete the content of a temporary table in SAP HANA?

Question

I create a temp table in SAP HANA this way:

  create local temporary table #sometable(table definition);

however, the delete statement is not supported on this table. i.e calling:

 delete from #sometable; 

will throw an error:

 feature not supported

When using this table within a stored procedure, the content is concatenated with the new data instead of being replaced with it. How can I resolve this issue?

Solution 1

What HANA Revision are you using? On the most recent Revision 68, it works just fine:

;drop table #sometable;
create local temporary table #sometable(id integer, str nvarchar(30));

insert into #sometable values (1,'one');
insert into #sometable values (2,'two');
insert into #sometable values (3,'three');

select * from #sometable;  --> returns 3 rows
delete from #sometable;
select * from #sometable;  --> returns 0 rows

Solution 2

I've faced the same problem recently and found next explanation in SAP Hana Developer Guide:

You can define the following values for the @Catalog.tableType annotation:

. . .

3) #GLOBAL_TEMPORARY

Set the scope of the created table. Data in a global temporary table is session-specific; only the owner session of the global temporary table is allowed to insert/read/truncate the data. A global temporary table exists for the duration of the session, and data from the global temporary table is automatically dropped when the session is terminated. A global temporary table can be dropped only when the table does not have any records in it.

That means you can clear temporary table using truncate:

truncate table #sometable;