用于SAP HANA的SQLScript是否支持将INSERT与CTE（公用表表达式）一起使用？ [英] Does SQLScript for SAP HANA support the use of INSERT with CTEs (Common Table Expressions)?

问题描述

我知道这不是特定的代码或问题，但是我遇到的问题与询问此问题的人非常相似（除了他们的问题是针对SQL Server的）：组合INSERT INTO和WITH / CTE ...我似乎无法在任何SAP HANA上找到它帮助论坛等。因此认为这里可能会有专家可以给我一个简单的是或否答案。

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.

我正在使用的SQL语句包含多个 CTE，但是当我尝试插入CTE时，它告诉我在INSERT一词周围存在语法错误。它的布局与我上面链接的问题（花费的小时数）完全相同，并且可以在必要时发布代码示例，但我只想知道是否首先支持它！谢谢

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

推荐答案

简短回答：
否，INSERT / UPDATE不支持CTE。

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

更长的答案：
SQLScript的INSERT / UPDATE命令实际上是借用的SQL命令，如文档说明。

检查SQL INSERT文档，我们发现它支持子查询作为值的来源。

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

子查询项被定义为SQL SELECT语句的一部分。查看 SELECT文档显示< subquery> 和< with_clause> 是不同的，不重叠的术语。

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.

这意味着CTE不能在子查询中使用，因此不能成为INSERT / UPDATE命令中使用的子查询的一部分。

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

但是，您可以在SQLScript块的INSERT语句中使用SQLScript 表变量，这与CTE非常相似：

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;

这篇关于用于SAP HANA的SQLScript是否支持将INSERT与CTE（公用表表达式）一起使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！