无法在 WITH 语句后删除表

2021-09-10 00:00:00 tsql sql-server

我有一个 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.

相关文章