Changing an internal table in an SQLScript Procedure

[Former Member]

Hi,

I'm trying to add rows to a table variable in an SQLScript Procedure, but this gives me syntax errors, so maybe it is not possible.What I'm trying to do is similar to this:

tab = SELECT col1, col2, col3 FROM aTable;

INSERT INTO :tab (col1, col2, col3) VALUES ('1', '2', '3');

But the INSERT line results in an error:

     Syntax error: 'tab' is incorrect or misplaced

Any ideas on how to get it to work?

I have got a version of the procedure running by using the new ARRAY feature from SP6, but it is rather slow (I have potentially a lot of rows to process); this is why I'd like to do it differently.

I was also thinking about creating an actual table temporarily and working on that, but that seems to be a lot of effort.

Regards,

Daniel.

[RichHeilman · Accepted solution]

Direct manipulation of intermediate table variables is not supported in HANA.  You can however do the same using a SELECT statement into a table variable.  But this would only be good for adding one row.

tab = SELECT '1' as col1,  '2' as col2, '3' as col3 FROM dummy;

You can insert multiple rows into an intermediate table variable simply by UNION the other tables together.

tab1 = SELECT '1' as col1,  '2' as col2, '3' as col3 FROM dummy;

tab2 = SELECT '4' as col1,  '5' as col2, '6' as col3 FROM dummy;

result = select * from :tab1

        union

      select * from :tab2;

select * from :result;

Cheers,

Rich Heilman

[Shky · Accepted solution]

This message was moderated.