执行 sp_executeSql for select...into #table 但无法选择临时表数据

Question

试图选择...到 sp_Executedsql 中的临时表#TempTable。 不是插入成功与否，而是写入了消息 （359行受影响）这意味着成功插入？ 下面的脚本

DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'select distinct Coloum1,Coloum2 into #TempTable 
            from SPCTable with(nolock)
            where Convert(varchar(10), Date_Tm, 120) Between @Date_From And @Date_To';

SET @Sql = 'DECLARE @Date_From VARCHAR(10);
            DECLARE @Date_To VARCHAR(10);
            SET @Date_From = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
            SET @Date_To = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
            '+ @Sql;

EXECUTE sp_executesql @Sql;

执行后，它向我返回消息（受影响的 359 行）。 接下来尝试从 #TempTable 中选择数据。

Select * From #TempTable;

它返回给我：

Msg 208, Level 16, State 0, Line 2
Invalid object name '#TempTable'.

怀疑它仅在“选择”部分起作用。插入件不起作用。 如何修复它？

Answer 1

在这种情况下使用全局临时表可能会导致问题，因为该表将存在于会话之间，并且可能会导致异步使用调用代码时出现一些问题。

如果在调用 sp_executesql 之前定义了本地临时表，则可以使用它，例如

CREATE TABLE #tempTable(id int);

execute sp_executesql N'INSERT INTO #tempTable SELECT myId FROM myTable';

SELECT * FROM #tempTable;

Answer 2

本地临时表#table_name仅在当前会话中可见，全局临时表##table_name在所有会话中可见。两者都存活到会话结束为止。
sp_executesql - 创建自己的会话（也许单词“范围”会更好），这就是它发生的原因。

Answer 3

在您的 @sql 字符串中，请勿将 插入 #TempTable。相反，请调用不带 INSERT 语句的 SELECT 语句。

最后，将结果插入到临时表中，如下所示：

INSERT INTO @tmpTbl EXEC sp_executesql @sql

此外，如果使用此方法，则需要声明临时表

DECLARE @tmpTbl TABLE (
    //define columns here...
)

Answer 4

动态 SQL 中的临时表超出了非动态 SQL 部分的范围。

看看这里如何处理这个问题：关于sql server的本地临时表的一些信息

Answer 5

请注意，从 T-SQL 2021 开始，dm_exec_describe_first_result_set() 可用于构建正确形状的临时表以进行 INSERT INTO - 因为它为您提供将从您的数据库返回的列名称和类型。动态 SELECT 或 EXEC ...因此您可以构建动态 SQL 将临时表更改为您需要的形状。

DECLARE @strSQL NVarChar(max) = 'EXEC [YourSP] @dtAsAt=''2022-11-09'', @intParameter2=42'

--*** Build temporary table: create it with dummy column, add columns dynamically 
--*** using an exec of sys.dm_exec_describe_first_result_set()  and dropping the dummy column
DROP TABLE IF EXISTS #tblResults;
CREATE TABLE #tblResults ([zz] INT); 
DECLARE @strUpdateSQL NVarChar(max);
SELECT @strUpdateSQL = STRING_AGG( CONCAT(  'ALTER TABLE #tblResults ADD ', 
                                            QUOTENAME([name]), ' ', 
                                            [system_type_name], ';') 
                                , ' ')  WITHIN GROUP (ORDER BY [column_ordinal])
    FROM sys.dm_exec_describe_first_result_set (@strSQL, NULL, 0)
SET @strUpdateSQL += 'ALTER TABLE #tblResults DROP COLUMN [zz];'
EXEC (@strUpdateSQL);
    
--*** Now we have #tblResults in the right shape to insert into, and use afterwards
INSERT INTO #tblResults     EXEC (@strSQL);
SELECT * FROM #tblResults;
--*** And tidy up
DROP TABLE IF EXISTS #tblResults;

Answer 6

临时表的存在时间与创建临时表的连接一样长。我希望您无意中在单独的连接上发出选择。您可以通过暂时插入非临时表并查看数据是否存在来测试这一点。如果是这种情况，您可以返回到原始解决方案，并确保将连接对象传递给您的选择。

Answer 7

declare @sql varchar(1000)
set @sql="select * into #t from table;"
set @sql =@sql + "select * from #t;"

 execute  SP_EXECUTESQL  @sql

Answer 8

这对我有用

declare @sql nvarchar(max)     
create table #temp ( listId int, Name nvarchar(200))     
set @sql = 'SELECT top 10 ListId, Name FROM [V12-ListSelector].[dbo].[List]'    
insert into #temp
exec sp_executesql  @sql    
select * from #temp    
drop table #temp

Answer 9

要解决此问题，请在运行 sp_executesql 之前先使用 CREATE TABLE #TEMPTABLE 命令生成空临时表。然后使用 sp_executesql 运行 INSERT INTO #TEMPTABLE。这会起作用。这就是我克服这个问题的方法，因为我有一个设置，其中所有查询通常都是通过 sp_executesql 运行。

Answer 10

这对我有用：

DECLARE @Query as NVARCHAR(MAX);
SET @Query=(SELECT * FROM MyTable) ;
SET @Query=(SELECT 'SELECT * INTO dbo.TempTable FROM ('+@Query +') MAIN;');
EXEC sp_executesql @Query;

SELECT * INTO #TempTable FROM dbo.TempTable;
DROP TABLE dbo.TempTable;
SELECT * FROM #TempTable;