Save output of a CTE query to a temp table

Question

Save output of a CTE query to a temp table

Hursh 191

How do I save the output of this query to a temp table?

;WITH CTE1 AS
(
SELECT ID,CustNum,OrderNumber,Value, Description
,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY Value)AS [Counter]
FROM @test CROSS APPLY STRING_SPLIT(OrderNumber,'-')
),CTE2 AS
(
SELECT ID,CustNum,OrderNumber,Value,[Counter],Value AS Result, Description
FROM CTE1 WHERE [Counter] = 1
UNION ALL
SELECT C1.ID,C1.CustNum,C1.OrderNumber,C1.value,C1.[Counter],C2.Result+'-'+C1.value, C1.Description
FROM CTE1 C1 JOIN CTE2 C2 ON C1.ID = C2.ID AND C1.[Counter] = C2.[Counter] + 1
)
SELECT ID,CustNum,OrderNumber,[Counter],Value,Result
,LAG(Result)OVER(PARTITION BY ID ORDER BY [Counter])AS Misc
,CASE WHEN [Counter]=4 THEN 'NA' ELSE '' END AS Col1
,CASE WHEN [Counter]=2 THEN 'None' ELSE '' END AS Col2
,Description
FROM CTE2

--- DDL
DECLARE @test TABLE (ID INT PRIMARY KEY, CustNum INT, OrderNumber VARCHAR(MAX), Description VARCHAR(50));
INSERT @test VALUES
(9001, 1234, '11111-22222-33333-444444', 'Test1'),
(9002, 5678, '55555-66666-77777-888888', 'Test2');

LiHong-MSFT 10,056 Reputation points

2022-07-29T06:37:11.34+00:00

Hi @Hursh
Have you validate all the answers so far and provide any update?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

7 answers

Your answer

LiHong-MSFT 10,056 Reputation points

2022-07-29T06:37:11.34+00:00

Hi @Hursh
Have you validate all the answers so far and provide any update?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

   WITH  CTE1 AS (  
      ...  
   ). CTE2 AS (  
      ...  
   )  
   INSERT #temp(...)  
      SELECT ...  
      FROM  CTE2

Hursh 191 Reputation points

2022-07-24T22:52:38.103+00:00

I do not want to create #temp manually and I do not want to specify column names when inserting.
Is there a way to do something like this?

;WITH cteTemp AS
(
SELECT ID,CustNum,OrderNumber,[Counter],Value,Result
,LAG(Result)OVER(PARTITION BY ID ORDER BY [Counter])AS Misc
,CASE WHEN [Counter]=4 THEN 'NA' ELSE '' END AS Col1
,CASE WHEN [Counter]=2 THEN 'None' ELSE '' END AS Col2
,Description
FROM CTE2
)
SELECT * INTO #NewTable FROM cteTemp
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-07-25T08:16:13.927+00:00

The above should work. What error message do you get?

Since the above is fairly trivial, my assumption was that you already had created the temp table (which is best practice), but were not able to figure out the syntax for how to use a CTE with INSERT - which certainly is not entirely obvious the first time you see it.

Answer 2

Jingyang Li 5,896 Volunteer Moderator

You can:
SELECT * INTO #NewTable FROM cteTemp

Answer 3

Hi @Hursh
You could put the Select ...CASE WHEN ... into another CTE, like this:

DECLARE @test TABLE (ID INT PRIMARY KEY, CustNum INT, OrderNumber VARCHAR(MAX), Description VARCHAR(50));  
INSERT @test VALUES  
(9001, 1234, '11111-22222-33333-444444', 'Test1'),  
(9002, 5678, '55555-66666-77777-888888', 'Test2');  
;WITH CTE1 AS  
(  
SELECT ID,CustNum,OrderNumber,Value, Description  
,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY Value)AS [Counter]  
FROM @test CROSS APPLY STRING_SPLIT(OrderNumber,'-')  
),CTE2 AS  
(  
SELECT ID,CustNum,OrderNumber,Value,[Counter],Value AS Result, Description  
FROM CTE1 WHERE [Counter] = 1  
UNION ALL  
SELECT C1.ID,C1.CustNum,C1.OrderNumber,C1.value,C1.[Counter],C2.Result+'-'+C1.value, C1.Description  
FROM CTE1 C1 JOIN CTE2 C2 ON C1.ID = C2.ID AND C1.[Counter] = C2.[Counter] + 1  
),CTE3 AS  
(  
SELECT ID,CustNum,OrderNumber,[Counter],Value,Result  
,LAG(Result)OVER(PARTITION BY ID ORDER BY [Counter])AS Misc  
,CASE WHEN [Counter]=4 THEN 'NA' ELSE '' END AS Col1  
,CASE WHEN [Counter]=2 THEN 'None' ELSE '' END AS Col2  
,Description  
FROM CTE2  
)  
SELECT * INTO #TEMP FROM CTE3  
  
SELECT * FROM #TEMP

Best regards,
LiHong

Answer 4

;WITH CTE1 AS
(
SELECT ID,CustNum,OrderNumber,Value, Description
,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY Value)AS [Counter]
FROM @test CROSS APPLY STRING_SPLIT(OrderNumber,'-')
),CTE2 AS
(
SELECT ID,CustNum,OrderNumber,Value,[Counter],Value AS Result, Description
FROM CTE1 WHERE [Counter] = 1
UNION ALL
SELECT C1.ID,C1.CustNum,C1.OrderNumber,C1.value,C1.[Counter],C2.Result+'-'+C1.value, C1.Description
FROM CTE1 C1 JOIN CTE2 C2 ON C1.ID = C2.ID AND C1.[Counter] = C2.[Counter] + 1
)
SELECT ID,CustNum,OrderNumber,[Counter],Value,Result
,LAG(Result)OVER(PARTITION BY ID ORDER BY [Counter])AS Misc
,CASE WHEN [Counter]=4 THEN 'NA' ELSE '' END AS Col1
,CASE WHEN [Counter]=2 THEN 'None' ELSE '' END AS Col2
,Description
INTO #TempTable
FROM CTE2

Answer 5

;WITH CTE1 AS
(
SELECT ID,CustNum,OrderNumber,Value, Description
,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY Value)AS [Counter]
FROM @test CROSS APPLY STRING_SPLIT(OrderNumber,'-')
),CTE2 AS
(
SELECT ID,CustNum,OrderNumber,Value,[Counter],Value AS Result, Description
FROM CTE1 WHERE [Counter] = 1
UNION ALL
SELECT C1.ID,C1.CustNum,C1.OrderNumber,C1.value,C1.[Counter],C2.Result+'-'+C1.value, C1.Description
FROM CTE1 C1 JOIN CTE2 C2 ON C1.ID = C2.ID AND C1.[Counter] = C2.[Counter] + 1
)
SELECT ID,CustNum,OrderNumber,[Counter],Value,Result
,LAG(Result)OVER(PARTITION BY ID ORDER BY [Counter])AS Misc
,CASE WHEN [Counter]=4 THEN 'NA' ELSE '' END AS Col1
,CASE WHEN [Counter]=2 THEN 'None' ELSE '' END AS Col2
,Description
into #temp
FROM CTE2

Share via

Save output of a CTE query to a temp table

7 answers

Your answer