Insert cte query result into Temp table

Question

I want to save the result of this query in the 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 

Thanks in advance.

Answer 1

Assuming this is for SQL Server: CTE is only suitable for statement one - so you cannot have both SELECT and INSERT - just use 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 

This requires that #MyTempTable already exists. If you want to create it using SELECT , use this syntax:

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