0

I know this isn't a specific bit of code or problem, but I am having trouble with a very similar issue to the person asking this (except theirs is for SQL Server): Combining INSERT INTO and WITH/CTE ...and I can't seem to find it out there on any SAP HANA help forums etc. so thought there may be an expert on here who can just give me a simple yes or no answer.

The SQL statement I am using contains multiple CTEs, but when I try to insert it tells me there is a Syntax error around the word INSERT. It is definitely laid out exactly the same as in the question I've linked above (spent hours checking), and I can post code samples if necessary but I simply want to know whether it is supported first! Thanks

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
jackelsey
  • 169
  • 1
  • 5
  • 17

3 Answers3

2

Short answer: No, CTEs are not supported for INSERT/UPDATE statements.

Longer answer: SQLScript's INSERT/UPDATE commands are actually "borrowed" SQL commands as the documentation explains.

Checking the documentation for SQL INSERT we find that it supports a subquery as a source of values.

The subquery term is defined as part of the SQL SELECT statement. Checking the documentation for SELECT shows that <subquery> and <with_clause> are different, non-overlapping terms.

This means, that CTEs cannot be used in subqueries and therefore not be part of the subqueries used in INSERT/UPDATE commands.

You can, however, use SQLScript table variables in INSERT statements in your SQLScript blocks, which is very similar to CTEs:

DO BEGIN
  te_a := SELECT 10, 'xyz' as VAL from dummy;
  te_b := SELECT 20, 'abc' as VAL from dummy;

  te_all :=     SELECT * from :te_a
      UNION ALL SELECT * from :te_b;

  INSERT INTO VALS 
             (SELECT * from :te_all);

END;
Lars Br.
  • 9,949
  • 2
  • 15
  • 29
0

You can convert the CTE into a Sub-Select statement in many cases

You can use following

insert into city (city, countryid, citycode)
select
    city, countryid, citycode
from (
    -- CTE Expression as subselect
    select * from city
    -- end (CTE)
) cte

Instead of using following valid CTE command combined with INSERT (on SQL Server)

with cte as (
    select * from city
)
insert into city (city, countryid, citycode)
select
    city, countryid, citycode
from cte
Eralper
  • 6,461
  • 2
  • 21
  • 27
  • Hi. Thanks for your answer. I'm aware you can convert them and would normally do so for one or two CTEs - I was just really hoping not to have to as I had about 15 that all refer to each other and it looked like it could get pretty messy. I'm using a Calculation View rather than a static table and INSERT statements to overcome this for now – jackelsey Feb 19 '18 at 11:52
0

SAP HANA includes this posibility, the order of the code is different than SQL Server:

INSERT INTO EXAMPLE (ID)
 WITH cte1 AS (SELECT 1 AS ID FROM DUMMY)
 SELECT ID FROM cte1;
user12360869
  • 1