CODEWITHSUNDEEP
sqlsql-insertcommon-table-expressionhana
Bala
Bala

Reputation: 107

How to use WITH CLAUSE ...INSERT query in SAP HANA?

Here I used With AS Clause.if i use SELECT query it is working fine but if i use insert query . it gives syntax error. Can we use WITH ....INSERT in SAP HANA?

Code:

WITH t1 as
(
  Select 
  col1,
 col2,
 col3
  from table1),

t2 as
(
select 
a.col4,
a.col5,
a.col1,
b.col3
from table2 a 
left outer join t1
on a.col1 = b. col1)
insert into table3
select
c.col4,
c.col5,
c.col3
from t2;

Upvotes: 3

Views: 13677

Answers (2)

Lars Br.
Lars Br.

Reputation: 10396

In addition to Serban's correct answer, a general workaround for lack of CTE functionality is to create views instead. In your case that could be:

create view t1 as
(select 
        col1,
        col2,
        col3
 from 
        table1);

create view t2 as
(select 
      a.col4,
      a.col5,
      a.col1,
      b.col3
 from 
      table2 a 
 left outer join t1
              on a.col1 = b. col1);

insert into table3
select
      c.col4,
      c.col5,
      c.col3
from t2;

Upvotes: 1

Serban Petrescu
Serban Petrescu

Reputation: 5206

Based on my knowledge on HANA, CTEs (~ WITH-based queries) are currently not supported in INSERT clauses. This means that you should directly use sub-queries instead where possible.

IMO, the only scenario that is impossible to create without CTEs are recursive queries (which are not at all supported in HANA). As your query is not recursive, you can re-write and simplify it as follows:

INSERT INTO TABLE3
SELECT T2.COL4, T2.COL5, T1.COL3 
    FROM TABLE1 AS T1 
    LEFT OUTER JOIN TABLE2 AS T2
    ON T1.COL1 = T2.COL1

Upvotes: 0

Related Questions