Manipulate Temporary Tables in Stored Procedures

Stored procedures can reference temporary tables that are created during the current session.

Temporary Table Names Beginning with “#”

Temporary tables with names beginning with “#” that are created within stored procedures are not saved when the procedure exits.

A single procedure can:

Create a temporary table
Insert data into the table
Run queries on the table
Call other procedures that reference the table

Use create table to create the temporary table.
Create the procedures that access the temporary table, but do not create the procedure that creates the table.
Drop the temporary table.
Create the procedure that creates the table and calls the procedures created in step 2.

Temporary Table Names Beginning with tempdb..

You can use create table tempdb...tablename from inside a stored procedure to create temporary tables without the # prefix. These tables persist when the procedure completes, so they can be referenced by independent procedures.

Follow the steps for temporary table names beginning with “#” to create these tables.

Warning! Create temporary tables with the “tempdb..” prefix from inside a stored procedure only if you intend to share the table among users and sessions. Stored procedures that create and drop a temporary table should use the “#” prefix to avoid inadvertent sharing.