执行 sp_executeSql for select...into #table 但无法选择出临时表数据 [英] Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data

问题描述

试图选择...进入 sp_Executedsql 中的临时表 #TempTable.没有成功插入，但那里写了消息(359 行受影响) 表示成功插入?下面的脚本

Was trying to select...into a temp Table #TempTable in sp_Executedsql. Not its successfully inserted or not but there Messages there written (359 row(s) affected) that mean successful inserted? Script below

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 中选择数据.

After executed,its return me on messages (359 row(s) affected). Next when trying to select out the data from #TempTable.

Select * From #TempTable;

它还给我:

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

怀疑它仅适用于选择"部分.插入不工作.怎么解决?

Suspected its working only the 'select' section only. The insert is not working. how fix it?

推荐答案

本地临时表 #table_name 仅在当前会话中可见，全局临时 ##table_name 表是在所有会话中可见.两者都存在，直到他们的会话结束.sp_executesql - 创建它自己的会话(也许范围"这个词会更好)所以这就是它发生的原因.

Local temporary table #table_name is visible in current session only, global temporary ##table_name tables are visible in all sessions. Both lives until their session is closed. sp_executesql - creates its own session (maybe word "scope" would be better) so that's why it happens.

这篇关于执行 sp_executeSql for select...into #table 但无法选择出临时表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！