2

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

ronron555
  • 21
  • 1
  • 1
  • 3

6 Answers6

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.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
0

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';
Eralper
  • 6,461
  • 2
  • 21
  • 27
0

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
ozmike
  • 2,738
  • 1
  • 33
  • 40
0

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

create local temporary table #tempTableName' like "tableTypeName";
Taazar
  • 1,545
  • 18
  • 27
Gforce3FG
  • 11
  • 1
0

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
cgage1
  • 579
  • 5
  • 15
0

I suppose you can just write :

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

BR,

D'Hib
  • 1