将带有CTE查询的结果插入到临时表中 [英] Inserting the result of a with cte query into a Temp Table

问题描述

我想将此查询的结果存储到临时表中：

I want to store the result of this query into a temp table:

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

预先感谢。

推荐答案

假设这是用于 SQL Server 的内容：CTE仅适合一个语句-因此不能同时使用 SELECT 和 INSERT -只需使用插入：

Assuming this is for SQL Server : the CTE is good for only one statement - so you cannot have both a SELECT and an INSERT - just use the 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 创建它，请使用以下语法：

This requires that the #MyTempTable already exists. If you want to create it with the SELECT - use this syntax:

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

这篇关于将带有CTE查询的结果插入到临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！