1

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?

  • Hi, Could you add what you already tried tot the question? Thanks! – Randi Vertongen Apr 9 at 18:09
  • @RandiVertongen See update. – Edmund Apr 9 at 18:31
0

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 Apr 10 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.