Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 916
Mani Avatar asked Jun 01 '12 11:06

Mani


People also ask

Can a CTE be used multiple times?

Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.

How do you use multiple CTE in one query?

The first CTE is separated from the second one by a comma. This also goes if you write more than two CTEs: all the CTEs are separated by a comma. However, no matter how many CTEs you have, there's no comma between the last CTE and the main query.

Can I use CTE inside view?

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

Can CTE have an alias?

A Common Table Expression (better known as a CTE) is a temporary table expression that is defined directly above an outer query. The CTE contains an inner query, and is given an alias. That alias is referenced in the FROM clause of the outer query.


2 Answers

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.

like image 74
MatBailie Avatar answered Oct 05 '22 18:10

MatBailie


A CTE is only in scope for the SQL statement it belongs to. If you need to reuse its data in a subsequent statement, you need a temporary table or table variable to store the data in. In your example, unless you're implementing a recursive CTE I don't see that the CTE is needed at all - you can store its contents straight in a temporary table/table variable and reuse it as much as you want.

Also note that your DELETE statement would attempt to delete from the underlying table, unlike if you'd placed the results into a temporary table/table variable.

like image 41
MartW Avatar answered Oct 05 '22 18:10

MartW