0

I have a temp table with few table names as below:

Id   TableName
-------------
1    TableA
2    TableB
3    TableC

These table names should be replaced in the query shown, and should get executed. I can achieve it with a while loop, but I don't want to use while. Is there any alternative concept?

I have the following SQL statement:

SELECT 
    TDU.ColHeader, TDU.ColValues 
FROM 
    (SELECT 
         ' + @ColumnsCasted + ' 
     FROM 
         ' + @TableName + ' ' + 
     @WhereCondition + ') TD
UNPIVOT 
     (ColValues FOR ColHeader IN (' + @ColumnsUnpivot + ')
     ) AS TDU;

The @TableName, @ColumnsCasted, @ColumnsUnpivot are based upon the table name which is stored in a temp table. I used while loop to iterate each table name and replace it here in the statement.

Can anyone suggest a method without using while loop?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Srinivasan Rajasekaran
  • 45
  • 6
  • 1
    Can you add some sample input so that the question can be made more clear – Jayasurya Satheesh Dec 26 '18 at 06:00
  • 1
    There is (probably) no set based way to do what you want so there is no other way other than using a procedural approach (while or cursor). Nor is there actually any problem in doing so. – Nick.Mc Dec 26 '18 at 06:45

2 Answers2

2

You may try to generate and execute dynamic SQL. Next example is just for SELECT * FROM Table statement, you must change it for your query:

-- Create table
CREATE TABLE #TempTable (
   Id int,
   TableName nvarchar(50)
)
INSERT INTO #TempTable (Id, TableName)
VALUES 
   (1, 'TableA'),
   (2, 'TableB'),
   (3, 'TableC')

-- Dynamic SQL
DECLARE @stm nvarchar(max)
DECLARE @err int

SET @stm = N''
SELECT @stm = 
   @stm +
   'SELECT * FROM ' + 
   QUOTENAME(TableName) +
   '; '
FROM #TempTable

-- Statement execution
PRINT @stm   
EXEC @err = sp_executesql @stm
IF @err <> 0 PRINT 'Error'
ELSE PRINT 'OK'

Generated statement:

SELECT * FROM [TableA]; SELECT * FROM [TableB]; SELECT * FROM [TableC];
Zhorov
  • 28,486
  • 6
  • 27
  • 52
1

There is non-documented function sp_MSforeachtable that executes query for each table in databse, maybe it can help you:

EXEC sp_MSforeachtable 'SELECT COUNT(*) FROM ?'
Backs
  • 24,430
  • 5
  • 58
  • 85