Skip to Content

How to store data from table data type to a real table in HANA?

Hi,

I am trying to copy the whole table's data to a table data type and then performing the calculation on it and then deleting the real table and storing the data of table data type to the real table but its not working.

Create procedure multiply(OUT RESULTS TABLE (FNAME NVARCHAR(10), Lname NVARCHAR(20), ID INTEGER, AGE Integer, DOJ DATE, CITY NVARCHAR(10), SALARY DECIMAL(10,2), RESULT DECIMAL(10,2)))

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

Default Schema T4H

/*READS SQL DATA*/ AS

BEGIN

Declare tab1 table(FNAME NVARCHAR(10), Lname NVARCHAR(20), ID INTEGER, AGE Integer, DOJ DATE, CITY NVARCHAR(10), SALARY DECIMAL(10,2), RESULT DECIMAL(10,2));

tab1 = select FNAME as FNAME,Lname as Lname,ID as ID,AGE as AGE,DOJ as DOJ,CITY as CITY, SALARY as SALARY, salary*10 as Result from EMPLOYEE;

Update EMPLOYEE set Result=null;

Delete from Employee;

Update Employee set Employee.Result = :tab1.Result;

Employee = select FNAME as FNAME,Lname as Lname,ID as ID,AGE as AGE,DOJ as DOJ,CITY as CITY, SALARY as SALARY, salary*10 as Result from EMPLOYEE;

Results=Select * from Employee;

END;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 01, 2017 at 07:41 AM

    Table types cannot carry any data as they only describe how data is structured.

    A table variable can in turn can carry data of that table type - which you have done in your code.

    Up until HANA2 table variables cannot be modified via SQL. To do something like insert or update actual tables, including temp tables are necessary.

    All that you can find in the programming guides...

    Also, please remove unrelated tags from your question. This has nothing to do with SQL Anywhere.

    Add comment
    10|10000 characters needed characters exceeded