Sql-server – How to create and fill multiple session temp tables with dynamic SQL

dynamic-sqljsonsql-server-2017temporary-tables

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON keys and their types such that I can construct a strings that contain the text to create a temp table and pivot the results of OPENJSON to INSERT into that temp table.

However, I am having issues dynamically creating the temp tables with EXECUTE( @stmt ), EXEC sp_executesql @stmt and the OPENROWSET adhoc distributed query trick. 

DECLARE @dynamicCreateStmt NVARCHAR(MAX),
        @dynamicPivotInsertStmt NVARCHAR(MAX);

--CREATE statement for #temp table
SET @dynamicCreateStmt = '< some combination of string constants and results of STRING_AGG>'

--PIVOT - INSERT statement for #temp table
SET @dynamicPivotInsertStmt = '< some combination of string constants and results of STRING_AGG>'

--First try
EXEC (@dynamicCreateStmt);
EXEC (@dynamicPivotInsertStmt);
SELECT * FROM #temp into #Working1

<repeat for various "working" tables>

--Second try
EXEC sp_executesql @dynamicCreateStmt;
EXEC sp_executesql @dynamicPivotInsertStmt;
SELECT * FROM #temp into #Working1

<repeat for various "working" tables>

--Third try; tries to skip intermediate table.
DECLARE @dynamicPivotStmt NVARCHAR(MAX);

--PIVOT statement for #temp table
SET @dynamicPivotStmt = '< some combination of string constants and results of STRING_AGG>'

SELECT * INTO #Working1 FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;Database=MyDB;',
     @dynamicPivotStmt)

<repeat for various "working" tables>

The first 2 will execute but the temp table created is not in the current scope. The last wont run because a variable cannot be supplied as the query parameter.

EXECUTE ... INTO is not an option as that is limited to one occurrence per run and I need to dynamically create additional tables as the run progresses.

Any ideas of how to dynamically create and fill multiple session temp tables
at different locations in the same run?

Best Answer

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 = '< some combination of string constants and results of
STRING_AGG __ Use @TableName here to define the table name for your session only>'

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.

Related Question