CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Sandman
Sandman

Reputation: 2307

Using the result of a CTE to query from table without cursor

I need to query from a table which is a result of a CTE query e.g.

;WITH CTE
AS
( 
  SELECT TableName
    FROM dbo.TableContainingListOfTableNames        
)
SELECT * FROM CTE

This will return me the names of the tables which I need to query data from.

Names of tables returned from table store

Can I use the CTE to query from the respective tables e.g. SELECT * FROM dbo.[1006UN]? I know I could use a temp table to store these values and use a cursor to iterate with dynamic sql but I do not want to use a cursor, if at all possible.

I have tried something as simple as:

SELECT * FROM dbo.[CTE]

Which gives me the error:

Invalid object name 'dbo.CTE'.

And have went further to attempt to use dynamic SQL with the CTE:

DECLARE @query1 VARCHAR(MAX)
SELECT @query1 = N';WITH CTE
AS
(
  SELECT TableName  
    FROM dbo.TableContainingListOfTableNames    
)
SELECT * FROM dbo.["' + CTE + '"] '

EXECUTE(@query1)

Which gives me the error:

Invalid column name 'CTE'.

So firstly, is it possible to achieve this? And if not, are there any alternatives which can achieve this (avoiding cursors)?

Upvotes: 2

Views: 747

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Can be done. It's irrelevant if you use CTE or not. Once you have your tables names you can create and concatenate dynamic SELECT statements. 

DECLARE @query NVARCHAR(MAX) = '';

WITH CTE AS
( 
  SELECT TableName
  FROM dbo.TableContainingListOfTableNames        
)
SELECT @query = @query + 'SELECT * FROM ' + QUOTENAME(TableName) + '; '
FROM CTE;

PRINT @query; --run print first to check
EXEC (@query);

Upvotes: 4

Related Questions