CODEWITHSUNDEEP
sqlsql-serverhana
Rafael Reyes
Rafael Reyes

Reputation: 2655

Error Insert Into a table variable Hana

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 "tab_table_TYPE" AS TABLE ("DocEntry" integer, "LineId" integer, "VisOrder" integer, "Object" nvarchar(20));  
 v_Tab tab_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 @v_Tab([DocEntry],[LineId],[VisOrder],[Object]) exec (@query)

I wish to use an internal table type variable which can hold the resultset from the query!

When I use the Sql Converter with this sentence displays 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?

Upvotes: 1

Views: 6703

Answers (1)

hoffman
hoffman

Reputation: 420

The syntax of your table-type creation is correct. I guess you are trying to execute the query inside a procedure.

Inside a procedure, you have different options. If your query is only a partial result and you want to run further statements on the result set of the query, you don't need to initialize a table variable at all. Just assign a variable to a resultset:

table_variable = SELECT DocEntry, LineId, VisOrder, Object FROM INV_AFC;

// Use table_variable for further statements, for example:
SELECT count(*) INTO resultSetCount FROM :table_variable;

If your query is already the final result, you can easily define an output variable and directly assign your result set as output variable. For example:

CREATE PROCEDURE "YOURSCHEMA"."SomeProcedureName" (
      in someInputVariable1 NVARCHAR(255),
      in someInputVariable2 BIGINT,
      out tableVariable "YOURSCHEMA".tab_table_TYPE)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER AS
BEGIN
    tableVariable = SELECT DocEntry, LineId, VisOrder, Object FROM INV_AFC;
END;

When you then call this procedure the 3rd parameter will automatically contain your result set:

call "YOURSCHEMA"."SomeProcedureName"('someString', 123, ?);

Upvotes: 3

Related Questions