How to create and fill multiple session temp tables with dynamic SQL?
Creating temp tables dynamically means you won't be able to access them in your session as you've discovered because they're scoped to a session and the dynamic execution is not actually in your session scope.

You can get around this by creating global temporary tables (##tablename instead of #tablename) instead which are accessible across open sessions.

If you have multiple sessions running the same query, make sure you dynamically assign a name that will be unique to your session, for example:

DECLARE @TableName NVARCHAR(255)

SELECT @TableName = '##MyTempTable_' + CAST(@@SPID AS NVARCHAR(5))

--CREATE statement for #temp table
SET @dynamicCreateStmt = '&lt; some combination of string constants and results of
STRING_AGG __ Use @TableName here to define the table name for your session only&gt;'


This should allow you to dynamically create and use your temp table but interact with it in isolation to your scope, even if running multiple instances of this query concurrently.