Delete temporary table if it exists in HANA

[Former Member]

Hi,

Is there a command to drop the table if it exists while writing stored procedures in HANA ?

I am getting 'cannot use duplicate table name exception:' error on executing my stored procedure.

Thanks.

[Former Member · Accepted solution]

The answer is no, there is not a command to drop a table if it exists.

If it is a global temporary table you may try something like this:

CREATE GLOBAL TEMPORARY TABLE YOUR_TABLE (ONE INTEGER);

declare created INTEGER;
created := 0;
select count(*) into created from tables where schema_name ='YOUR_SCHEMA' and table_name='YOUR_TABLE' and IS_TEMPORARY = 'TRUE';
IF (:created > 0) THEN
  DROP TABLE YOUR_TABLE
END IF

I'm not sure if this work with local temporary table, but it might worth the shot.

[Former Member · Accepted solution]

Hi

Trying to add few more points to jonathan code.

tables view is under SYS schema. with SP9 DB version I can see additional column 'TEMPARORY_TABLE_TYPE' in tables view which might be useful.

Regards,

Venkat N.

[Former Member · Accepted solution]

Hi Jonathan,

Thanks for the reply but i want to drop the table (if it already exists somewhere in memory) prior to creating it.  Something like :

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL

BEGIN

  DROP TABLE #VisitsTemp1

END

in MS SQL Server

[Former Member · Accepted solution]

Hi,

Also, I am not able to create autosequencing for ID column in a temporary table. I tried using ::ROWCOUNT but it failed. Also I tried creating sequence inside the stored procedure and use some_seq.nextval but it says that creating sequence is not allowed inside stored procedure. Could anyone find a good method to do it?

Thanks.

[Former Member · Accepted solution]

If YOu can Use global temporary table, only it has to be truncated to be dropped automatically when last session is closed. Read it up.

If you have to use #temp table..then there's a way, if You think about it ..I Wrote example for You, hopefully no explanation will  be needed

DROP PROCEDURE "DK"."TEST_TEMP_TABLE";

CREATE PROCEDURE "DK"."TEST_TEMP_TABLE" (

   OUT counter bigint )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA "DK"

   AS

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

DROP TABLE "#TESTDK";

END;

CREATE LOCAL TEMPORARY TABLE "#TESTDK" (ID INTEGER);

END;

PS: counter is relict from my wrapper proc which I reuse.

[Former Member]

Thanks a lot for the answer Dubravko and Thank you all for the help. EXIT HANDLER seems to do the job.

@Martin I could not find M_TEMPORARY_TABLES. Can you pls tell where to look for it in the Catalog.

Thanks,

Nikhil

[Former Member]

You can find it in M_MONITORS:

select * from m_monitors where view_name = 'M_TEMPORARY_TABLES'

[Former Member]

Just don't forget to "clean your garbage" so DROP  your #temptable at the end of proc

Also try out Martins solutions, I also first tried to find table for metadata for #temptable, but I couldn't find it, so i went with this "try-catch->dontcare" implementation with exit handler..

Thx Martin for that valuable info from mee too.

[former_member182302]

Hi Nikhil,

Local Temporary Table is session specific. If it says duplicate table name, then when you call the procedure for the first time you have created it and have not dropped it.

So in your stored procedure, if you are creating the local temporary table, then ensure you are writing a DROP Temporary table at the end of procedure before completing your execution.

This will ensure that when you are calling the same procedure again in the same session, you will not encounter that error.

Regards,

Krishna Tangudu

[Former Member]

You can check column TABLE_NAME of monitoring view M_TEMPORARY_TABLES for the table in question. If it exists, you can proceed to drop it. But this will only work if currently no session has records in it.

[Former Member]

Try this solution out

Anyway it would be ok if you give Code to see what might be wrong ?