SAP HANA does not support InsertTarget

[Former Member]

Hi, I'm working passing ms sql server statements into hana sql statements. I have a variable (Tab) type Table and a variable string (query) defined as:

Hana Statement

CREATE TYPE "example_table_TYPE" AS TABLE ("DocEntry" integer, "LineId" integer, "VisOrder" integer, "Object" nvarchar(20));
 Tab example_table_TYPE

query := 'SELECT [DocEntry],[LineId],[VisOrder] ,[Object] FROM [@INV_AFC]';

so I'm trying to convert this Ms Sql Statement into a Hana Statement :

Ms Sql Server Statement

INSERT INTO @Tab([DocEntry],[LineId],[VisOrder],[Object]) exec (@query)

It just insert values into a Table Variable ( defined above ) Tab when the exec runs, but the Sql Conversor to Hana throws this error:

--[Note:ErrorModifier] INSERT INTO statement does not support EXEC; use EXEC('insert into table '+ originalstatement)

--[Note:Stringifier] SAP HANA does not support InsertTarget

Finally the question is : How would be the correct Hana Sql Statement for this case?

[former_member184768 · Accepted solution]

Hi Reyes,

It is bit difficult to comment on your issue without having the complete overview of the requirement. But just looking at the code, if you wish to use an internal table type variable which can hold the resultset from the query then you can use HANA syntax something like:

v_Tab = SELECT "DocEntry","LineId","VisOrder","Object" from "INV_AFC";

You can then use v_Tab for further processing.

Alternatively, you can use temporary tables in the procedure and insert data to the temporary table and process it further. As I mentioned, it depends on the requirement.

Regards,

Ravi

[Former Member · Accepted solution]

Thanks for your answer Ravindra, so you I can understand that the correct Hana Statement would be:

v_Tab = SELECT "DocEntry","LineId","VisOrder","Object" from "INV_AFC";  exec(:query)    

Executes the query and the resulset is stored in v_Tab by using the SELECT "DocEntry","LineId","VisOrder","Object" from "INV_AFC"

is that OK?

[former_member182302 · Accepted solution]

You can either use EXEC with a insert statement into temporary table OR EXECUTE IMMEDIATE.

PFB the below examples:

Using EXEC:

Using EXECUTE IMMEDIATE:

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Thanks for your answer Krishna, I've been reading your examples and I have 2 questions about it.

1 ) Using EXECUTE INMEDIATE

Hana Statement

query := 'SELECT  DocEntry,LineId,VisOrder ,Object FROM @INV_AFC';

EXECUTE IMMEDIATE(:query)

Question : but it just execute the query .. then How can I insert the resultset of the query into my table variable(Tab)  to use Tab for further processing ?

2 ) Using EXEC with a insert statement into Table variable is possible?

Thanks in advance!

[former_member182302 · Accepted solution]

You can use EXEC to insert the results into Temporary table and then use SELECT on Temp Table to your Table variable.

Regards,

Krishna Tangudu