SQL Server - 在 INSERT 语句中使用 WITH 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15574281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - using the WITH clause in an INSERT statement
提问by Macs Dickinson
I was wondering if this was possible. I have an existing query that uses the WITHclause to apply some aggregated data to a SELECTquery 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 INSERTthe results of this query into another table.
我现在想把INSERT这个查询的结果放到另一个表中。
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'.
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”附近的语法不正确。
关键字“with”附近的语法不正确。如果此语句是公用表表达式或 xmlnamespaces 子句，则前一条语句必须以分号结束。
Is what I am trying to do possible with different some different syntax?
我正在尝试用不同的一些不同的语法做可能吗？
回答by Taryn
You will need to place the INSERT INTOright after the CTE. So the code will be:
您需要将INSERT INTO右侧放在CTE. 所以代码将是：
;WITH alias (y,z)
AS
(
SELECT y,z FROM tableb
)
INSERT INTO tablea(a,b)
SELECT y, z
FROM alias
回答by John Woo
Another way without using a CTEis by wrapping it in a subquery,
另一种不使用 a 的方法CTE是将其包装在子查询中，
INSERT INTO tablea(a,b)
SELECT y, z
FROM
(
SELECT y,z FROM tableb
) alias
回答by CoOl
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 语句提供结果集。
