CODEWITHSUNDEEP
sqlsql-server
Srinivasan Rajasekaran
Srinivasan Rajasekaran

Reputation: 45

How to iterate in SQL Server without while loop

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?

Upvotes: 0

Views: 2259

Answers (2)

Zhorov
Zhorov

Reputation: 30003

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

Upvotes: 2

Backs
Backs

Reputation: 24913

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

Upvotes: 1

Related Questions