T-SQL: multiple usage of CTE alias - not only in outer query

Question

I've got a question which occurs when I was using the WITH-clause in one of my script. The question is easy to pointed out I wanna use the CTE alias multiple times instead of only in outer query and there is crux.

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 row will lead to the following error because it's outside the outer query:

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

Is there a way to use the CTE multiple times resp. make it persistent? My current solution is to create a temp table where I store the result of the CTE and use this temp 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
[...]

Frankly, I don't like this solution. Does anyone else have a best practice for this problem?

Thanks in advance!

Answer 1

A CommonTableExpression doesn't persist data in any way. It's basically just a way of creating a sub-query in advance of the main query itself.

This makes it much more like an in-line view than a normal sub-query would be. Because you can reference it repeatedly in one query, rather than having to type it again and again.

But it is still just treated as a view, expanded into the queries that reference it, macro like. No persisting of data at all.

This, unfortunately for you, means that you must do the persistance yourself.

• If you want the CTE's logic to be persisted, you don't want an in-line view, you just want a view.

• If you want the CTE's result set to be persisted, you need a temp table type of solution, such as the one you do not like.