使用sp_executeSql执行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'.

怀疑只有“select”部分在工作，插入操作没有生效。如何解决？

- Worgon

Answer 1

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

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

CREATE TABLE #tempTable(id int);

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

SELECT * FROM #tempTable;

- Rob Willis

Answer 2

本地临时表 #table_name 仅在当前会话中可见，全局临时表 ##table_name 在所有会话中可见。两者都会一直存在，直到它们所在的会话结束。

sp_executesql - 创建了它自己的会话（也许单词“范围”更好），因此会出现这种情况。

- Michał Powaga

---

我认为使用"scope"一词会更好。 DECLARE @sql NVARCHAR(MAX); SET @sql = 'SELECT @@SPID'; EXECUTE sp_executesql @sql; SELECT @@SPID - Tom Hunter

谢谢@Michal，我已经想起来这种使用方法了。再次感谢。 - Worgon

问题是如何修复它，而不是为什么它会出错。我在下面提供了正确的答案。INSERT INTO @tmpTbl EXEC sp_executesql @sql - Mark Entingh

注意：sp_executesql不会创建自己的会话，而是创建自己的批处理（或执行上下文）。临时表可以被同一会话中的其他批处理看到。但是，由于它们在创建它们的批处理退出时被删除，实际上只能被从属批处理看到（即由创建临时表的同一上下文创建的执行上下文）。 - RBarryYoung

如果有多个人尝试运行使用这些全局变量的过程，难道不会出现死锁和其他问题吗？ - Elizabeth

Answer 3

在您的@sql字符串中，不要插入into #TempTable。相反，调用没有INSERT语句的SELECT语句。

最后，像这样将结果插入到您的临时表中：

INSERT INTO @tmpTbl EXEC sp_executesql @sql

此外，如果您采用这种方法，您需要声明临时表

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

- Mark Entingh

---

虽然这样做是可行的，但是按照现有的写法它是不完整的。问题在于，在 OP 的原始示例中，“select ... into ... #TempTable ...”实际上会创建临时表，包括动态规划列的规格。为了使您的语句起作用，必须首先创建/声明临时表（或表变量，因为您正在使用），并正确/匹配列规格。 - RBarryYoung

啊，是的，我忘了提到这一点。你肯定需要先声明你的临时表。 - Mark Entingh

Answer 4

你在动态SQL中创建的临时表不在非动态SQL部分的范围内。

看一下这里如何处理：关于SQL Server的本地临时表

- Mladen Prajdic

Answer 5

临时表只在创建它们的连接存在期间存在。我认为您可能无意中在另一个连接上发出了select语句。您可以通过将插入操作更改为非临时表并查看数据是否存在来进行测试。如果是这种情况，您可以回到原始解决方案，并确保将连接对象传递给您的select语句。

- Carth

---

这并不完全正确。当使用像PreparedStatements这样的东西时，MSSQL引擎会将其通过存储过程运行以执行，这意味着任何临时表都会在存储过程的上下文中保留，并且如果您尝试执行一个PreparedStatement，然后是一个select * from tempTable，您将得到一个tempTable未找到的错误。 - Chris Knoll

Answer 6

这对我很有帮助

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

- WebBoy

Answer 7

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

 execute  SP_EXECUTESQL  @sql

- Raviprasad Bettigeri

Answer 8

为解决这个问题，首先使用CREATE TABLE #TEMPTABLE命令生成一个空的临时表，然后再运行带有sp_executesql的INSERT INTO #TEMPTABLE。这样就可以解决问题了。这是我克服这个问题的方法，因为我的设置通常使用sp_executesql运行所有查询。

- Simon Darlow

Answer 9

注意，从 T-SQL 2021 开始，可以使用 dm_exec_describe_first_result_set() 来构建一个临时表，以便将其插入到正确的形状中 - 因为它会给出从动态 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;

- AjV Jsy

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;

- Hpeck