問題描述

我有一個 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 語句后刪除表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！