SQL 执行 sp_executeSql for select...into #table 但无法选择出临时表数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8040105/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data
提问by Worgon
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
试图选择...进入 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;
After executed,its return me on messages (359 row(s) affected). Next when trying to select out the data from #TempTable.
执行后，它会返回消息（受影响的 359 行）。接下来尝试从#TempTable 中选择数据时。
Select * From #TempTable;
Its return me:
它返回我：
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?
怀疑它仅适用于“选择”部分。插入不工作。如何解决？
采纳答案by Micha? Powaga
Local temporary table #table_nameis visible in current session only, global temporary ##table_nametables 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.
局部临时表#table_name仅在当前会话##table_name中可见，全局临时表在所有会话中可见。两者都存在，直到他们的会话结束。
sp_executesql- 创建自己的会话（也许“范围”这个词会更好），这就是它发生的原因。
回答by Rob Willis
Using a global temporary table in this scenario could cause problems as the table would exist between sessions and may result in some problems using the calling code asynchronously.
在这种情况下使用全局临时表可能会导致问题，因为该表会存在于会话之间，并且可能会导致异步使用调用代码出现一些问题。
A local temporary table can be used if it defined before calling sp_executesql e.g.
如果在调用 sp_executesql 之前定义了本地临时表，则可以使用它，例如
CREATE TABLE #tempTable(id int);
sp_executesql 'INSERT INTO #tempTable SELECT myId FROM myTable';
SELECT * FROM #tempTable;
回答by Mark Entingh
In your @sqlstring, don't insert into #TempTable. Instead, call your SELECTstatement without an INSERTstatement.
在您的@sql字符串中，不要插入into #TempTable. 相反，请在SELECT没有声明的情况下调用您的INSERT声明。
Finally, insert the results into your temporary table like so:
最后，将结果插入到临时表中，如下所示：
INSERT INTO @tmpTbl EXEC sp_executesql @sql
Also, you'll need to declare the temporary table if you use this approach
此外，如果您使用这种方法，则需要声明临时表
DECLARE @tmpTbl TABLE (
//define columns here...
)
回答by Mladen Prajdic
your temp table in dynamic SQL is out of scope in the non dynamic SQL part.
您在动态 SQL 中的临时表超出了非动态 SQL 部分的范围。
Look here how to deal with this: A bit about sql server's local temp tables
回答by Carth
Temporary tables only live as long as the connection that creates them. I would expect that you're unintentionally issuing the select on a separate connection. You can test this by momentarily doing your insert into a non-temporary table and seeing if your data is there. If that is the case you can go back to your original solution and just be sure to pass the connection object to your select.
临时表的存在时间与创建它们的连接一样长。我希望您无意中在单独的连接上发出选择。您可以通过暂时插入非临时表并查看您的数据是否在那里来测试这一点。如果是这种情况，您可以返回原始解决方案，并确保将连接对象传递给您的选择。
回答by WebBoy
This worked for me
这对我有用
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
回答by Raviprasad Bettigeri
declare @sql varchar(1000)
set @sql="select * into #t from table;"
set @sql =@sql + "select * from #t;"
execute SP_EXECUTESQL @sql
回答by Simon Darlow
To work around this issue use a CREATE TABLE #TEMPTABLE command first to generate an empty temp table before running sp_executesql. Then run the INSERT INTO #TEMPTABLE with sp_executesql. This will work. This is how I overcome this problem as I have a setup in which all my queries are usually run via sp_executesql.
要解决此问题，请在运行 sp_executesql 之前首先使用 CREATE TABLE #TEMPTABLE 命令生成一个空的临时表。然后使用 sp_executesql 运行 INSERT INTO #TEMPTABLE。这将起作用。这就是我克服这个问题的方法，因为我有一个设置，其中我的所有查询通常都通过 sp_executesql 运行。
