我可以在不声明表的情况下从 SP 插入临时表吗?

2021-09-10 00:00:00 tsql sql-server

这是我的代码:

alter procedure test1 as
select DeptID,DeptName from Department
go

alter procedure test2 as
--Create Table #tab (DeptID INT, DeptName VARCHAR(255))
INSERT INTO #tab
exec test1
select * from #tab
drop table #tab
go

exec test2

我收到类似无效的对象名称#tab"的错误

I am getting an error like "Invalid object name #tab"

如果我在开头添加 Create Table #tab (DeptID INT, DeptName VARCHAR(255)) 那么我不会收到任何错误.

If I add at the begining Create Table #tab (DeptID INT, DeptName VARCHAR(255)) then I do not get any error.

我的代码有什么问题?我可以在不声明临时表及其列定义的情况下根据存储过程的结果填充临时表吗?

What is wrong in my code? Can I populate a temp table from the results of a stored procedure without declaring the temp table and its column definitions?

推荐答案

从存储过程加载临时表时，必须先创建表.

When loading a temp table from a stored procedure, then you have to CREATE the table first.

没有直接的等价物

SELECT * INTO #temptable FROM AnotherTable

非直截了当的版本(阅读关于如何在存储之间共享数据程序".而且更简单)会

The non-straightforward version (read all about the bad stuff on "How to Share Data Between Stored Procedures". And simpler) would be

SELECT * INTO #temptable FROM OPENQUERY(Loopback, 'exec test1')

相关文章