T-SQL: multiple use of CTE aliases - not just in an external query

Question

I have a question that arises when I used the WITH clause in one of my script. The question is easy to indicate that I want to use the CTE alias several times, and not just in the external request, and there is a difficult question.

For instance:

-- Define the CTE expression WITH cte_test (domain1, domain2, [...]) AS -- CTE query ( SELECT domain1, domain2, [...] FROM table ) -- Outer query SELECT * FROM cte_test -- Now I wanna use the CTE expression another time INSERT INTO sometable ([...]) SELECT [...] FROM cte_test 

The last line will result in the following error, since it is outside the external query:

Msg 208, Level 16, State 1, Line 12 Invalid object name 'cte_test'.

Is there a way to use CTE several times or. make it persistent? My current solution is to create a temporary table in which I store the result of the CTE and use this temporary table for any further statements.

 -- CTE [...] -- Create a temp table after the CTE block DECLARE @tmp TABLE (domain1 DATATYPE, domain2 DATATYPE, [...]) INSERT INTO @tmp (domain1, domain2, [...]) SELECT domain1, domain2, [...] FROM cte_test -- Any further DML statements SELECT * FROM @tmp INSERT INTO sometable ([...]) SELECT [...] FROM @tmp [...] 

Honestly, I do not like this solution. Does anyone else have best practice for this problem?

Thanks in advance!

Answer 1

The CommonTableExpression expression does not save data in any way. This is basically just a way to create a subquery up to the most basic query.

This makes it look more like a string representation than a regular subquery. Because you can refer to it several times in one query, instead of typing it again and again.

But it is still just seen as a view expanded into queries that reference it like a macro. Lack of data at all.

This, unfortunately, for you means that you must fulfill your resistance.

• If you want the CTE logic to be preserved, you do not need the built-in view, you just want to view.

• If you want the CTE result set to be preserved, you will need a temp table solution type, such as one you don't like.

Answer 2

CTE is only available for the SQL statement to which it belongs. If you need to reuse your data in the next statement, you will need a temporary table or table variable to store the data. In your example, if you are not using a recursive CTE, I don’t see that a CTE is needed at all - you can save its contents directly in a temporary table / table variable and reuse it as much as you want.

Also note that your DELETE statement will attempt to delete from the base table, unlike if you put the results in a temporary table / table variable.