CODEWITHSUNDEEP
sqlhana
tNunes5
tNunes5

Reputation: 3

How do I work with table variables with multiple rows in SAP HANA DB?

In SQL sometimes it is easier and faster to use table variables.

I know I can't use insert to table var in HANA DB, but what would be the best practice to do something similar?

I tried using SELECT to populate the variable but I can't insert multiply rows.

Do I have to use temporary table instead?

I would like to have a table with some values I create, like the below example I use for SQL, such a way I can use it later in the query:

Declare @temp table(Group Int, Desc nvarchar(100))
    insert into @temp (Group , Desc )
        Values (1,'Desc 1'), (2,'Desc2 2'),  (3,'Desc 3'),  (4,'Desc 4'),  (5,'Desc 5')

In HANA, I am able to create the variable, but not able to populate it with multiple rows :(

Is there any best way to do so?

Thank you so much.

Upvotes: 0

Views: 5385

Answers (2)

tNunes5
tNunes5

Reputation: 3

For who else try to find about it, I found a "work around" that is to use the UNION ALL.

I add the first row, then I do a UNION on the table and ADD the second line, like below:

tempTable = Select 1 as "Group", 'Desc' as "Desc" FROM DUMMY;
tempTable = SELECT "Group", "Desc" FROM :AcctClassificacao UNION ALL Select 2 as "Group",
            'Desc' as "Desc" FROM DUMMY ;

Select * from tempTable

In this case, I will have the result:

Group   Desc
  1     Desc
  2     Desc

I don't know if this is the best way to do anyways.

Upvotes: 0

Lars Br.
Lars Br.

Reputation: 10388

The "UNION"-approach is one option to add records to the data that a table variable is pointing to.

Much better than this is to either use arrays to add, remove, and modify data and finally turn the arrays into table variables via the UNNEST function. This is an option that has been available for many years, even with HANA 1.

Alternatively, SAP HANA 2 (starting with SPS 03, I believe), offers additional SQLScript commands, to directly INSERT, UPDATE, and DELETE on table variables. The documentation covers this in "Modifying the Content of Table Variables". Note, that this feature comes with a slightly different syntax for the DML commands.

As of SAP HANA 2 SPS 04, there is yet another syntax option provided for this: "SQL DML Statements on Table Variables". This one, finally, looks like "normal" SQL DML against table variables.

Given these options, the "union"-approach is the last option you should use in your coding.

Upvotes: 1

Related Questions