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

Question

Here is my code:

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

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?

Do you want to populate a temp table with the results of a Stored Procedure without having to first create the table definition? Basically, a dynamically created temp table? — codingbadger, Jun 16 '11 at 07:57
Possible duplicate of [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) — underscore_d, Apr 19 '18 at 12:30

score 4 · Accepted Answer · answered Jun 16 '11 at 08:02

4

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

answered Jun 16 '11 at 08:02

gbn

408,740
77
567
659

what is loop back here any way i solved the problem....thanks.alter procedure test1 as select DeptID,DeptName from Department go alter procedure test2 as SELECT * INTO #tab FROM OPENROWSET('SQLNCLI', 'server=192.168.1.12\bbareman;trusted_connection=yes', 'set fmtonly off exec MyTest.dbo.test1') select * from #tab drop table #tab go – Mou Jun 16 '11 at 08:37

Pankaj · Answer 2 · 2011-06-16T08:19:14.680

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

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

2 Answers2

Linked