How to create and fill multiple session temp tables with dynamic SQL?

Question

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?

David Browne - Microsoft · Accepted Answer · 2021-07-04 15:32:41Z

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

Dynamic queries and stored procedure invocations are both "nested scopes". Existing temp are visible in nested scopes, but temp tables created in nested scopes are automatically dropped at the end of the nested scope.

So the solution is to create all your temp tables in the outer scope, and insert into them in the inner scope. Of course this doesn't work for a dynamic pivot, whose columns aren't known in the outer query. But the results of a final dynamic pivot can be sent to the client.

HandyD · Accepted Answer · 2019-04-10 00:56:00Z

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.

Global dynamic names are not workable as I would need the entire body of the stored procedures to be strings in order to use these sorts of temp tables. That is just not an option I would even begin to consider as it is not practical. — Edmund
– Edmund, Commented Apr 10, 2019 at 1:24

Stack Exchange Network

How to create and fill multiple session temp tables with dynamic SQL?

2 Answers 2

Your Answer

Hot Network Questions

How to create and fill multiple session temp tables with dynamic SQL?

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions