使用CTE的结果从表中查询而不使用游标

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

我需要从一个表中查询，这个表是CTE查询的结果。

;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.

这将返回需要查询数据的表的名称。

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.

我是否可以使用CTE从各自的表中查询，例如从dbo中选择*。[1006UN]我知道我可以使用一个临时表来存储这些值，并使用游标对动态sql进行迭代，但是如果可能的话，我不想使用游标。

I have tried something as simple as:

我试过一些简单的方法:

SELECT * FROM dbo.[CTE]

Which gives me the error:

这给了我一个错误:

Invalid object name 'dbo.CTE'.

无效的对象名称“dbo.CTE”。

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

并进一步尝试在CTE中使用动态SQL:

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'.

无效列名“CTE”。

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

那么首先，有可能实现这一点吗?如果没有，有没有其他方法可以实现这个(避免游标)?

1 个解决方案

#1

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.

可以做到的。是否使用CTE无关紧要。一旦有了表名，就可以创建和连接动态选择语句。

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);