Creating a SQL Loop to Change the Table Name

Question

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?

The easiest way to debug dynamic SQL is to PRINT it. If you did that, you'd see that table[1] is not a valid object name. This type of SQL, however, strongly suggests you have design flaw. — Thom A
– Thom A ♦, Commented Feb 8, 2021 at 19:10

WAMLeslie · Accepted Answer · 2021-02-08 19:05:21Z

2

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

answered Feb 8, 2021 at 19:05

WAMLeslie

1,2711 gold badge7 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dylan Garland Over a year ago

Wonderful. Thank you. I will mark it as accepted once it allows me to.

WAMLeslie Over a year ago

Excellent. Thanks.

Charlieface · Accepted Answer · 2021-02-08 20:27:01Z

0

You can still do UNION ALL, you just need to construct the dynamic query all at once, with no loops.

DECLARE @sql nvarchar(max) =
'INSERT INTO #Accounts (Account)
' +
(
SELECT STRING_AGG(
    N'Select COUNT(ACCT) From dbo.' + QUOTENAME(N'table_' + i),
    CAST(N'
UNION ALL
' AS nvarchar(max)))
FROM (VALUES('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('11'),('12') ) AS v(i)
);

EXEC (@sql);

I question, though, why the column in the temp table is varchar if you are inserting a COUNT

answered Feb 8, 2021 at 20:27

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

2 Comments

Dylan Garland Over a year ago

Some of the characteristics were not changed when I created this general example. I have to loop through month/year combinations 12.2019 through 12.2020, so the first answer works best.

Charlieface Over a year ago

If you supply exactly what you need, I'm sure that could be done without a loop also. Sounds like a tally table join

Collectives™ on Stack Overflow

Creating a SQL Loop to Change the Table Name

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related