CODEWITHSUNDEEP
sqlsql-server
HappyFreddie
HappyFreddie

Reputation: 135

How to keep temporary table after running stored procedure?

I know the temporary table will be deleted after the connection is lost. But within that connection, I want to do something like

EXEC test;

SELECT * FROM #Final;

#Final is the temporary table created in the stored procedure. The stored procedure needs 30 seconds, and I want to check my #final without running stored procedure again.

If I run the script in that stored procedure, the #final can be reused in the connection. But how to use it after the EXEC test?

So, except for creating a real table, is it possible to SELECT * FROM #Final after EXEC test? If no, I'll use real table instead. Thanks!

Upvotes: 0

Views: 947

Answers (2)

Usman Khan
Usman Khan

Reputation: 1

Use a global temporary Table

create proc demo as 
begin
 select * into ##temptable from original_table
end

exec demo;

select * from ##temptable

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Then you don't want a temporary table. Use either a global temporary table (##final) or a real table.

Then delete the results after you run the procedure.

I should note that the stored procedure can return a result set which you can insert into a table, using exec().

Upvotes: 2

Related Questions