'Temporary tables in hana

it it possible to write script in hana that crate temporary table that is based

on existing table (with no need to define columns and columns types hard coded ):

 create local temporary table #mytemp (id integer, name varchar(20));

create temporary table with the same columns definitions and contain the same data ? if so ..i ill be glad to get some examples

i am searching the internet for 2 days and i couldn't find anything useful

thanks

Solution 1:^[1]

Creating local temporary tables based on dynamic structure definition is not supported in SQLScript.

The question would be: for what do you want to use it? Instead of a local temp. table you can use a table variable in most cases.

Solution 2:^[2]

By querying sys.table_columns view, you can get the list and properties of source table and build a dynamic CREATE script then Execute to create the table. You can find SQL codes for a sample case at Create Table Dynamically on HANA Database

For table columns read

select * from sys.table_columns where table_name = 'TABLENAME';

Solution 3:^[3]

Seems to work in the hana version I have. I'm not sure how to find out what the version.

PROCEDURE "xxx.yyy.zzz::MY_TEST"(

    OUT "OUT_COL" NVARCHAR(200) 
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   AS
BEGIN
    create LOCAL TEMPORARY TABLE #LOCALTEMPTABLE 
    as
    (
    SELECT distinct 'Cola' as out_col 
        FROM "SYNONYMS1"
    );
    select * from #LOCALTEMPTABLE ;
    DROP TABLE #LOCALTEMPTABLE;
END

Solution 4:^[4]

The newer HANA version (HANA 2 SPS 04 Patch 5 ( Build 4.4.17 )) supports your request:

create local temporary table #tempTableName' like "tableTypeName";

Solution 5:^[5]

This should inherit the data types and all exact values from whatever query is in the parenthesis:

CREATE LOCAL COLUMN TEMPORARY TABLE #mytemp AS (
    SELECT 
    "COLUMN1",
    "COLUMN2",
    "COLUMN3"
    FROM MyTable 
    );
 -- Now you can add the rest of your query here as such: 
SELECT * FROM #mytemp

Solution 6:^[6]

I suppose you can just write :

create column table #MyTempTable as ( select * from MySourceTable);

BR,

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Lars Br.
Solution 2	Eralper
Solution 3	ozmike
Solution 4	Taazar
Solution 5	cgage1
Solution 6	D'Hib

'Temporary tables in hana

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]