HANA SQL - Insert from procedure local table with less number of values

[former_member527468]

Hello Experts,

In a HANA procedure, I have a local table variable whose data is populated by a select statement. Now, I need to insert these values to a database table which has more number of columns that are not known to me during design time.

drop table t1; 
CREATE TABLE t1(a INT, b INT, c INT);  
do 
begin 
declare query nvarchar(100);

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
local_tab = select 1 as a , 2 as b from dummy; 
-- case 1
insert into t1 select * from :local_tab; 
-- case 2
query = 'insert into t1 ( a , b ) select * from :local_tab'; 
exec query;  
end

case 1 : When I try an direct insert statement with a select sub query from the local table, I get the error 'not enough values: B'.

case 2: When I try to create a dynamic query by deriving the insert column list, I get an error invalid use of table variable: LOCAL_TAB.

While I understand that these two errors may be as expected, is there anyway I can achieve this goal?

Thanks,

Ajith

[pfefferf · Accepted solution]

Ok, thx for the additional information given in the comments.

If you know the columns than it should be easy to do with dynamic SQL as you are on the latest SPS.

Your dynamic SQL from above failed, because you assumed that ":local_tab" should be adressed directly by just writing it to the statement string. This does not work. But you can make that work with the "USING" addition for dynamic SQL. Check the details in the documentation please, there are detailed examples listed.

In your case the dynamic statement then should look like following (of course the statement string has to be created dynamically to consider the column names):

...
query = 'insert into t1 ( a , b ) select * from :local_tab'; 
exec query using :local_tab;  
...

[former_member527468 · Accepted solution]

Thank you so much Florian!!!

It worked. This is really helpful.

Thanks again to you and SCN,

Ajith