Creating a SQL Loop to Change the Table Name

I am needing to change the ending number of the table name in a loop to go from 1 through 12. This is a more straight forward example of my actual problem, but if this can be solved, then I can duplicate the results later on (Actual problem is using the past 12 months on a rolling basis for a report and all of the individual months are stored by themselves). I know I can create a string query with each query being unioned to the other, but I still have to edit all 12 queries when a change is needed. I am looking for a more efficient process.

Declare @Query VARCHAR(MAX)
DECLARE @i INT
CREATE TABLE #Accounts (Account varchar(10))
SET @i = 1


WHILE @i <= 12
BEGIN
Select @Query = 'Select COUNT(ACCT) From dbo.table_'+quotename(@i)
   INSERT INTO #Accounts
    EXEC(@Query)
   SET @i = @i + 1;
END;

SELECT Account FROM #Accounts

When I run this code, I get invalid object names for the table because it doesn’t attached the number to the end of the name. Will this be possible using a while loop or will I have to start looking into cursors?

Answer

Your QUOTENAME should be around the entire table name.

Declare @Query VARCHAR(MAX)
DECLARE @i INT
CREATE TABLE #Accounts (Account varchar(10))
SET @i = 1


WHILE @i <= 12
BEGIN

print quotename(@i)
Select @Query = 'Select COUNT(ACCT) From dbo.'+quotename('table_'+convert(nvarchar,@i))
   INSERT INTO #Accounts
   EXEC(@Query)
   SET @i = @i + 1;
END;

SELECT Account FROM #Accounts