如何使用動態 SQL 創建和填充多個會話臨時表？

我有一列包含 JSON 數據。該表可以針對在 JSON 列中具有大部分相同 JSON 鍵的類似 JSON 數據集進行過濾。我可以動態收集一組 JSON 鍵及其類型，以便我可以構造一個包含文本的字元串來創建一個臨時表，並將 OPENJSON 的結果轉為 INSERT 到該臨時表中。

但是，我在動態創建臨時表時遇到了問題EXECUTE( @stmt )，EXEC sp_executesql @stmt以及OPENROWSET臨時分佈式查詢技巧。

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>

前 2 個將執行，但創建的臨時表不在目前範圍內。最後一個不會執行，因為無法提供變數作為查詢參數。

EXECUTE ... INTO不是一個選項，因為每次執行只能出現一次，我需要隨著執行的進行動態創建其他表。

關於如何在同一執行中在不同位置動態創建和填充多個會話臨時表的任何想法？

動態創建臨時表意味著您將無法在會話中訪問它們，因為它們的範圍僅限於會話，而動態執行實際上不在會話範圍內。

您可以通過創建全域臨時表（##tablename 而不是#tablename）來解決此問題，這些臨時表可在打開的會話中訪問。

如果您有多個會話執行相同的查詢，請確保動態分配一個對您的會話唯一的名稱，例如：

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>'

這應該允許您動態創建和使用您的臨時表，但與您的範圍隔離地與它互動，即使同時執行此查詢的多個實例。

關於如何在同一執行中在不同位置動態創建和填充多個會話臨時表的任何想法？

動態查詢和儲存過程呼叫都是“嵌套範圍”。現有的臨時表在嵌套範圍內可見，但在嵌套範圍內創建的臨時表會自動刪除到嵌套範圍的末尾。

所以解決方案是在外部範圍內創建所有臨時表，並在內部範圍內插入它們。當然，這不適用於動態樞軸，其列在外部查詢中是未知的。但是最終動態樞軸的結果可以發送給客戶端。

引用自：https://dba.stackexchange.com/questions/234341