插入的与CTE查询结果到一个临时表

Question

我想把这个查询的结果存储到一个临时表：

WITH cOldest AS 
(
    SELECT 
     *, 
     ROW_NUMBER() OVER (PARTITION BY [MyKey] ORDER BY SomeColumn DESC) AS rnDOB 
    FROM MyTable 
) 
SELECT 
    C.* 
*** Insert into #MyTempTable *** This part doesn't work 
    FROM 
    cOldest C 
    WHERE 
    C.rnDOB = 1 

在此先感谢。

Answer 1

假设这是SQL服务器：热膨胀系数是好的只有一个声明 - 所以你不能同时拥有SELECT和INSERT - 只使用INSERT：

WITH cOldest AS 
(
    SELECT 
     *, 
     ROW_NUMBER() OVER (PARTITION BY [MyKey] ORDER BY SomeColumn DESC) AS rnDOB 
    FROM MyTable 
) 
INSERT INTO #MyTempTable(Col1, Col2, ....., ColN) 
    SELECT Col1, Col2, ...., ColN 
    FROM cOldest C 
    WHERE C.rnDOB = 1 

这就要求#MyTempTable已经存在。如果要用SELECT创建它 - 请使用以下语法：

WITH cOldest AS 
(
..... 
) 
SELECT c.* 
INTO #MyTempTable 
FROM cOldest c 
WHERE C.rnDOB = 1