SQL Server - 在INSERT语句中使用WITH子句

I was wondering if this was possible. I have an existing query that uses the WITH clause to apply some aggregated data to a SELECT query like so: (massively simplified)

我想知道这是否可行。我有一个现有的查询使用WITH子句将一些聚合数据应用于SELECT查询，如下所示:(大规模简化）

;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
SELECT y, z FROM alias

I now want to INSERT the results of this query into another table.

我现在想将此查询的结果插入到另一个表中。

I have tried the following:

我尝试过以下方法：

INSERT INTO tablea(a,b)
;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
SELECT y, z FROM alias

but I get the error:

但我得到错误：

Incorrect syntax near ';'.

';'附近的语法不正确。

So I have tried without the semicolon but got the error:

所以我尝试了没有分号，但得到了错误：

Incorrect syntax near the keyword 'WITH'.

关键字“WITH”附近的语法不正确。

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

关键字'with'附近的语法不正确。如果此语句是公用表表达式或xmlnamespaces子句，则必须以分号结束前一个语句。

Is what I am trying to do possible with different some different syntax?

我试图用不同的一些不同的语法做什么？

3 个解决方案

#1

You will need to place the INSERT INTO right after the CTE. So the code will be:

您需要在CTE之后立即放置INSERT INTO。所以代码将是：

;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
INSERT INTO tablea(a,b)
SELECT y, z 
FROM alias

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#2

Another way without using a CTE is by wrapping it in a subquery,

不使用CTE的另一种方法是将其包装在子查询中，

INSERT INTO tablea(a,b)
SELECT y, z 
FROM 
(
    SELECT y,z FROM tableb
) alias

#3

Semicolon is used to terminate the statement. So when you use ;WITH, terminates the previous statement. However, that's not why you are getting the error here. The problem here is with your INSERT INTO statement, which is looking for VALUES or SELECT syntax.

分号用于终止语句。因此，当您使用; WITH时，终止前一个语句。但是，这不是你在这里得到错误的原因。这里的问题是你的INSERT INTO语句，它正在寻找VALUES或SELECT语法。

INSERT INTO statement can be used in 2 ways - by providing VALUES explicitly or by providing a result set using SELECT statement.

INSERT INTO语句可以通过两种方式使用 - 通过显式提供VALUES或使用SELECT语句提供结果集。

#1