Can I insert into temp table from SP without declaring table?

Here is my code:

alter pro开发者_JAVA技巧cedure 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

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

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.

There is no straightforward equivalent of

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')

It's because the Local Temporary table #tab which you are expecting does not existing in the session.

So the table creation should not be commented line.

Create Table #tab (DeptID INT, DeptName VARCHAR(255))

Moreover, if you want to do without creating the table then it should be like below

Alter procedure test2 
As
Set NoCount ON
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
Begin
    Drop table #temp
End

SELECT DeptID, DeptName INTO #tab from Department
Select * from #tab
Drop table #tab

Go