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?