CODEWITHSUNDEEP
delete-rowhanatemp-tables
Mohamed Ali JAMAOUI
Mohamed Ali JAMAOUI

Reputation: 14699

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

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?

Upvotes: 2

Views: 13515

Answers (2)

bsiamionau
bsiamionau

Reputation: 8229

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;

Upvotes: 1

schmerdy - Sonos
schmerdy - Sonos

Reputation: 151

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

Upvotes: 1

Related Questions