cancel
Showing results for 
Search instead for 
Did you mean: 

In Hana SQL Script, can I insert records into an already existing table variable?

dustin_self2
Participant
0 Kudos

I am in need of help here, if somebody wouldn't mind taking the time.  If I have a Hana SQL Script procedure that inserts into a table variable, for example:

myTableVariable = select * from <dbTable>;

...Can I insert into :myTableVariable later on in the SQL Script?  For example:

insert into :myTableVariable (<field_list>) select * from <dbTable2>;

The end result would be records from <dbTable2> appended to records from <dbTable> inside :myTableVariable.

I know I can create a temp table to achieve this, but if I want my Hana SQL Script to be "read-only", then I can't create temp tables (at least, as far as I know.)

Thanks for any help you can provide!

Dustin

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

(Thanks for the reference Patrick)

Hi Dustin,

You are correct - can't do insert on table variables at all, and can't do insert to temp tables if you want to keep it read-only.

Can you provide more details on your core requirement? Should help in addressing alternatives.

For a workaround though, consider the following. In most programming langues (all?) you can assign a variable a value that results from some calculation against the same variable's prior value.

So, with some integer "c", this is, of course, valid: c = c + 1; // java and other C-like languages

(Of course, increment operator also works: c++;)

Think of this same approach to achieve what you need, but in database perspective.

Sample code:

a = SELECT 1 AS "MY_FIELD" FROM DUMMY;

-- append a record without INSERT

a = SELECT 2 AS "MY_FIELD" FROM DUMMY UNION ALL SELECT "MY_FIELD" FROM :a;

Hope that helps a bit.

Cheers

Jody

justin_molenaur2
Contributor
0 Kudos

I was thinking a similar thing as Jody. If the general requirement is that you need to insert contents from two tables into one target table just for intermediate processing, then it can be assumed that you can make the sources identical to the target.

This sounds like a perfect use case for a union, since you really are just trying to combine these together, no real need to "insert" so to speak.

temp_table = SELECT <COLUMNS> FROM <TABLE1>

UNION

SELECT <COLUMNS> FROM <TABLE2>

Or this can likely be achieved in another way using multiple variables,

a  = SELECT <COLUMNS> FROM <TABLE1>;
b = SELECT <COLUMNS> FROM <TABLE2>;

c = SELECT * FROM :a UNION SELECT * from :b;

Have not tested the above syntax as I'm not in front of a system, but seems logical.

Regards,

Justin

former_member182302
Active Contributor
0 Kudos

Adding to Justin's reply,

better use UNION ALL instead of UNION .

Regards,

Krishna Tangudu

justin_molenaur2
Contributor
0 Kudos

Hi Krishna, I guess (for me at least) it depends on the requirement. Just out of curiosity, what is the basis for your statement here?

Regards,

Justin

former_member182302
Active Contributor
0 Kudos

Hey Justin it was a very unclear reply from me .. apologies.. !!!

It depends on the requirement ( Yes for me too ) . As i was seeing the question where he got separate insert statements depending on different filter conditions, i assumed that it ensures that he doesn't have duplicate records coming up so proposed UNION ALL

Regards,

Krishna Tangudu

dustin_self2
Participant
0 Kudos

This helps a lot guys!  Thank you for the prompt response.

Jody, it make sense for me to use the concept of c = c + 1.  I applied this to my code to use CE_UNION_ALL to union 3 tables:

--tables AriesLoad1, AriesLoad2 and Reserves have table structures and are already filled with data.

FinalResult = ce_union_all(:AriesLoad1, :AriesLoad2);

FinalResult = ce_union_all(:FinalResult, :Reserves);

This seems to work good.  One thing I am wondering, though, is how to "merge" the data instead of doing a union.  The affect I'm looking for would be like doing an INNER JOIN on the tables while summing the key figures, and also including records in the result table that fall out due to the INNER JOIN.  Almost like in ABAP, using a COLLECT statement instead of an APPEND statement.  I don't know if there is a quick and clean way to do that in SQL Script.

I may just have to do an aggregation query after my union is complete:

FinalResult2 = select <fields>, sum(<key_figures>) from :FinalResult group by <fields>.

Does that make sense?

Dustin

justin_molenaur2
Contributor
0 Kudos

Makes sense to me, you want to aggregate based on one or more of the columns of the FinalResult2 Union result. I think you'll have to do this in two steps as you laid out. That being said, it might not actually create a plan that does it like this, there may be optimizations along the way.

If you are using CE Script you will likely want to use a CE_AGGREGATION on table FinalResult2 so you are not mixing SQLScript and CE.

To be honest, I would be interested to see comparisons of doing this in SQLScript vs. CE from a performance perspective. In SP06, I have done similar comparisons and actually found the SQLScript version to be faster.

Regards,

Justin

dustin_self2
Participant
0 Kudos

I don't have enough data in my sandbox tables to notice the difference between SQL Script and CE functions.  As we roll this out more, I'm sure I'll need to benchmark, as you are doing.

Thank you for the help with CE_AGGREGATION, hadn't seen that yet in the Help menu.

I appreciate your response!

Dustin

Former Member
0 Kudos

Also UNION ALL will have better performance as it just "stacks" two datasets without the rather expensive effort of removing duplicates.

justin_molenaur2
Contributor
0 Kudos

I wanted to quantify this difference, so I took a stab at it. Used a large dataset, and just performed a couple selects using fiscal year as the filters on each. I ran the same set of SELECTs, just changing the UNION to UNION ALL for the second iteration.

Conclusion is that UNION ALL is faster, but almost a negligible amount in this exercise. Likely to increase with result set size, but typically we'll be aggregating somewhat before the union even occurs.

SELECT COUNT(*)

FROM "SCHEMA_NAME"."CE12000"

WHERE "GJAHR" IN ('2013','2014')

RESULT -> 1,347,849,619

SELECT "GJAHR", "PERIO", "BUKRS", "SPART", "VKORG", "FKART", "WWMDC",SUM ("VVREV"),SUM("VVBBC")

FROM "SCHEMA_NAME"."CE12000"

WHERE "GJAHR" = '2013'

GROUP BY "GJAHR", "PERIO", "BUKRS", "SPART", "VKORG", "FKART","WWMDC"

UNION / (UNION ALL in second test)

SELECT "GJAHR", "PERIO", "BUKRS", "SPART", "VKORG", "FKART", "WWMDC",SUM ("VVREV"),SUM("VVBBC")

FROM "SCHEMA_NAME"."CE12000"

WHERE "GJAHR" = '2014'

GROUP BY "GJAHR", "PERIO", "BUKRS", "SPART","VKORG","FKART","WWMDC";


UNION DB average = 2.29s

UNION ALL DB average = 2.28s


The main difference from the VizPlan is the aggregation/de-duplication that takes place after the UNION. I was curious how this worked, so here are the plans.


UNION - notice the analytical search present after the union

UNION ALL - notice no analytical search, so it saves you the .01s

Regards,

Justin

former_member182302
Active Contributor
0 Kudos

Hi Justin,

My thoughts on seeing the Viz Plans:

Since UNION selects only distinct values, Analytic search is present to remove the duplicates ( if any ) so if the number of duplicate records are more then it would effect the overall performance

Regards,

Krishna Tangudu

justin_molenaur2
Contributor
0 Kudos

Good point - I was thinking the same. So I took it one step further and made the individual queries more granular and removed the WHERE clause columns from the result. This should make the analytic search de-duplication work harder in theory because the WHERE clause criteria is no longer in the result set, there is a lot of duplicates and we are also increasing the size.

SELECT DISTINCT "BUKRS", "SPART", "VKORG", "FKART", "WWMDC","PAPH1", "PAPH2"

FROM "SCHEMA_NAME"."CE12000"

WHERE "GJAHR" = '2013'

UNION (UNION ALL in second case)

SELECT DISTINCT "BUKRS", "SPART", "VKORG", "FKART", "WWMDC","PAPH1", "PAPH2"

FROM "SCHEMA_NAME"."CE12000"

WHERE "GJAHR" = '2014'

UNION VizPlan - Average runtime is 4.13s

UNION ALL VizPlan - average is 4.27s when I remove the first execution - which I saw as an outlier at 10.6s.

So in this case, UNION is still faster, by about 4% - which is interesting.

Regards,

Justin

Martin_Fei
Employee
Employee
0 Kudos

Interesting analysis and results. How about the memory footprint? It appears the additional analytic search is done on a temporary table, which looks like an intermediate result stored in memory. That would be another thing to consider especially when runtime is similar.

Answers (2)

Answers (2)

lucian2
Explorer
0 Kudos
patrickbachmann
Active Contributor
0 Kudos

Hi Dustin,

It's interesting you ask this question because this is essentially the reason I am using a write procedure because I have the need to do this same sort of thing.  In my case I had to write to a temp table to keep track.  In my thread you will see a comment from JODY where he says he mimics this behavior like you are asking.  Maybe he will chime in here with more details.

-Patrick