使用openrowset将Excel文件读入临时表；如何引用该表? [英] Using openrowset to read an Excel file into a temp table; how do I reference that table?

问题描述

我正在尝试编写一个存储过程，该存储过程将Excel文件读入临时表，然后处理该表中的某些数据，然后从该表中选择的行插入到永久表中.

I'm trying to write a stored procedure that will read an Excel file into a temp table, then massage some of the data in that table, then insert selected rows from that table into a permanent table.

所以，它是这样开始的:

So, it starts like this:

SET @SQL = "select * into #mytemptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+@file+";HDR=YES', 'SELECT * FROM [Sheet1$]')"

EXEC (@SQL)

这似乎行得通.

但是，如果我再尝试这样的话:

However, if I then try something like this:

Select * from #mytemptable

我得到一个错误:

无效的对象名称"#mytemptable"

为什么#mytemptable无法识别?有没有办法让其余存储过程可以访问#mytemptable?

Why isn't #mytemptable recognized? Is there a way to have #mytemptable accessible to the rest of the stored procedure?

非常感谢！

推荐答案

我没有时间来模拟它，所以我不知道它是否可行，但是尝试将表称为"## mytemptable"而不是"#mytemptable"

I don't have time to mock this up, so I don't know if it'll work, but try calling your table '##mytemptable' instead of '#mytemptable'

我猜您的问题是，在您执行exec()sql字符串后，您的表不再在作用域内.临时表前带有两个井号的符号是可全局访问的.

I'm guessing your issue is that your table isn't in scope anymore after you exec() the sql string. Temp tables preceded with two pound symbols are globally accessible.

别忘了放完它！

这篇关于使用openrowset将Excel文件读入临时表；如何引用该表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！