问题描述
我有一个 WITH
语句来收集我想要的数据.我想要做的是能够从我的 WITH
和 INSERT
的不同部分执行 SELECT
,从而生成目标表.
I have a WITH
statement that collects the data I want. What I want to do is to be able to do a SELECT
from different parts of my WITH
and INSERT
that result into a destination table.
这是我的长查询的简化版本:
Here is a simplified version of my long query:
WITH Active AS (
--SELECT 1
),
Inactive AS (
--SELECT 2
),
Churn AS (
--SELECT 3
)
--Drop destination table if exists
IF OBJECT_ID('DestinationTable', 'u') IS NOT NULL DROP TABLE DestinationTable;
SELECT Active.Name, Inactive.Name,Churn.Id
INTO DestinationTable
FROM Active a
JOIN Inactive i ON a.Id = i.Id
JOIN Churn c ON a.Id = c.Id;
但是Sql Server 不允许在WITH
之后直接使用IF
语句.我不想在我的 WITH
之前移动 IF
因为那样我的目标表很可能会长时间为空.
But Sql Server does not allow an IF
statement directly after a WITH
. I do not want to move the IF
before my WITH
because then it will be likely that my destination table be empty for a long time.
我的问题的简单版本:
如何将 SELECT
从 WITH
语句写入另一个表?
How can I write a SELECT
from a WITH
statement into another table?
推荐答案
就这样重构它:
--Drop destination table if exists
IF OBJECT_ID('DestinationTable', 'u') IS NOT NULL DROP TABLE DestinationTable
;WITH Active AS (
--SELECT 1
),
Inactive AS (
--SELECT 2
),
Churn AS (
--SELECT 3
)
SELECT Active.Name, Inactive.Name,Churn.Id
INTO DestinationTable
FROM Active a
JOIN Inactive i ON a.Id = i.Id
JOIN Churn c ON a.Id = c.Id;
CTE 的范围意味着它需要在声明后立即在语句中使用.
The scope of the CTE means it needs to be used in the statement immediately after it's declaration.
否则,在替换目标表之前,您需要一些其他暂存区来处理负载.
Otherwise you need some other staging area to handle the load before you replace the destination table.
这篇关于无法在 WITH 语句后删除表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!