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?