从 Excel 运行的 SQL 不能使用临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21417922/
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
SQL run from Excel cannot use a temporary table
提问by MattClarke
I have been able to create a data connection from Excel to SQL Server and execute many SQL queries successfully. But I cannot get any TSQL to work if it includes a temporary table. For example:
我已经能够创建从 Excel 到 SQL Server 的数据连接并成功执行许多 SQL 查询。但是如果它包含一个临时表，我就无法让任何 TSQL 工作。例如：
select * into #t from compass3.dbo.freq
select * from #t where freq_id>2
(Clearly there is no need to use #t in this case: I'm just giving the most simple example.) This work fine in SSMS but when executed via Excel I get the error message "We couldn't refresh the connection 'audbbicube'. The table 'ion Query1' may not exist."
（显然，在这种情况下不需要使用 #t：我只是举了一个最简单的例子。）这在 SSMS 中工作正常，但是当通过 Excel 执行时，我收到错误消息“我们无法刷新连接'audbbicube '。表 'ion Query1' 可能不存在。
In some other SO posts people suggested adding set nocount on, but that made no difference in this case.
在其他一些 SO 帖子中，人们建议添加set nocount on，但在这种情况下没有区别。
采纳答案by MattClarke
The following appears to work ...
以下似乎有效......
set nocount on
declare @t table(fid int) -- I'm sure I could add the rest of the columns if I wanted to
insert @t select freq_id from compass3.dbo.freq
select * from @t where fid>2
So as long as I turn nocounton and use a table variable rather than a temporary table, I can achieve what I need.
所以只要我打开nocount并使用表变量而不是临时表，我就可以实现我所需要的。
回答by arjunrc
I wanted to add to the above answer - just using SET NOCOUNT ONat the top of the query, with a regular temp table SELECT name INTO #Names FROM Employeeshould work.
我想添加到上面的答案 - 只需SET NOCOUNT ON在查询的顶部使用，使用常规临时表SELECT name INTO #Names FROM Employee应该可以工作。
A table variable is not needed here.
这里不需要表变量。
You could also add SET ANSI_WARNINGS OFFto avoid messages like "NULL Value is eliminated by an aggregate".
您还可以添加SET ANSI_WARNINGS OFF以避免诸如“NULL 值被聚合消除”之类的消息。
