SQL Server WITH 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28725389/
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 WITH statement
提问by user28455
My goal is to select result from one CTE and insert into other table with another CTE in the same procedure. How to do it?
我的目标是从一个 CTE 中选择结果，并在同一过程中用另一个 CTE 插入到另一个表中。怎么做？
My error is...
我的错误是...
invalid object name xy.
无效的对象名称 xy。
My query is
我的查询是
WITH ds
(
Select a, b, c
from test1
),
xy
(
select d, e, f
from test2
where (uses conditions from ds)
)
Select *
from ds (the result set of ds, am exporting this to csv)
Insert into AuditTest
(
Select * from xy
)
回答by D Stanley
A CTE is only good for one query, but it looks like you can use a CTE in each query:
CTE 仅适用于一个查询，但您似乎可以在每个查询中使用 CTE：
WITH ds AS
(
Select a, b, c from test1
)
Select * from ds (the result set of ds, am exporting this to csv)
WITH xy AS
(
select d,e,f from test2 where (uses conditions from test1)
)
Insert into AuditTest
(
Select * from xy
)
回答by Ceres
You actually can do both the insert and output the results using the OUTPUT clause to return the inserted rows.
您实际上可以使用 OUTPUT 子句执行插入和输出结果以返回插入的行。
;WITH ds AS
(
Select a, b, c from test1
),
xy AS
(
select d, e, f from test2 where (uses conditions from ds)
)
Insert into AuditTest
output inserted.d, inserted.e, inserted.f
Select d, e, f from xy
or a real test
或真正的测试
CREATE TABLE #Test (a int)
;WITH ds AS
(
Select 0 as a, 1 as b, 2 as c
),
xy AS
(
select a as d, b as e from ds
)
Insert into #Test
OUTPUT inserted.a
Select e from xy
回答by Hart CO
You can run the INSERTthusly, you can't run multiple queries after your cte:
你可以INSERT这样运行，你不能在你之后运行多个查询cte：
;WITH ds AS ( Select a, b, c
from test1
)
,xy AS ( select d,e,f
from test2
where (uses conditions from test1)
)
Insert into AuditTest
Select *
from xy
In this situation using temporary tables may be beneficial since you'll be re-running a query multiple times otherwise.
在这种情况下，使用临时表可能是有益的，因为否则您将多次重新运行查询。
