CODEWITHSUNDEEP
t-sqlcommon-table-expressionsql-server-2016
SUMguy
SUMguy

Reputation: 1673

TSQL - CTE/#Temp Table

In SSMS 2016 I have created a CTE and then immediately after the statement, I delete some rows from the CTE.

WITH cte AS 
(
   SELECT [GroupID], [UserID]+0.5, [Value] from dbo.myTable
)

DELETE FROM cte WHERE concat(GroupID, UserID) in (select concat(GroupID, UserID) as Concat from cte group by GroupID)

However, I want to then INSERT the remaining rows into the existing table, but when I try, I get the following error: "Invalid object name 'cte'."

I suspect the issue has something to do with the way CTEs work. As I am fairly new to them, I'm not sure, but it seems like a CTE can only be referenced once immediately following the WITH AS? Is that true? Is there a way around this? How can I insert data from the CTE?

I was thinking about using a temp table somehow, but I don't know if there's really a difference.

Upvotes: 0

Views: 84

Answers (1)

dfundako
dfundako

Reputation: 8324

cte's are one and done. You run one query with them and then they go away. You can use a temp table instead and that will persist for the duration of your session. 

SELECT [GroupID], [UserID], [Value] 
INTO #temp 
from dbo.myTable

DELETE FROM #temp 
WHERE concat(GroupID, UserID) in (select concat(GroupID, UserID) as Concat from #temp group by GroupID)

INSERT INTO your_table (col1, col2)
SELECT col1, col2 
FROM #temp

DROP TABLE #temp

As for the high level diff's between cte's and temp tables. Temp tables are physical storage and you can index them. CTE's are named subqueries and not stored as tabled/objects.

Upvotes: 0

Related Questions