How can I create tables in a loop?

Question

I was trying to create new tables using the identifier through a list.

DECLARE @Counter INT, @TableName NVARCHAR(20)
SET @Counter = 1
WHILE (@Counter <= 20)
BEGIN
     SELECT @TableName = TableName FROM [dbo].[TableList] WHERE index = @Counter
     SELECT * INTO [dbo].[@TableName.combine] FROM [dbo].[@TableName] t
     LEFT JOIN [dbo].[cost] c ON t.x = c.y
     SET @Counter = @Counter +1
END

And it keeps saying the object of [dbo].[@TableName] is invalid, but I already have [dbo].[@TableName] as a table. I looked over the declare table variable, but [dbo].[@TableName] already existed in the database, how can I point to the table that I want?

You cannot parameterize object names like this. Your code is literally trying to reference a table called `@TableName`, not a table with the name as the *value* of the `@TableName` variable. — Mark Rotteveel, Oct 14 '22 at 05:58
Can you go with `Exec(Sql)` , approach is to create complete statement as a string and then execute it like here [exec](https://stackoverflow.com/a/11689661/4018834) — Anel Hodžić, Oct 14 '22 at 07:42
Thanks for helping... I'll look at the loops for tables with dynamic sql then. — Ciceee, Oct 14 '22 at 10:29
This has [xy problem](https://xyproblem.info/) indications all over it. — Sean Lange, Oct 14 '22 at 13:16

score 0 · Accepted Answer · answered Oct 14 '22 at 11:18

You need to use dynamic SQL for this.

You can build one big query using STRING_AGG and then execute it

DECLARE @sql nvarchar(max);

SELECT @sql =
  STRING_AGG(CAST('
SELECT *
INTO dbo.' + QUOTENAME(tl.TableName + '.combine') + '
FROM dbo.' + QUOTENAME(tl.TableName) + ' t
LEFT JOIN dbo.cost c ON t.x = c.y;
'    AS nvarchar(max)), '
'  )
FROM dbo.TableList tl;

EXEC sp_executesql @sql;

How can I create tables in a loop?

1 Answers1