Overview
Sometimes you need to use a temporary table within a SQL Server stored procedure. How can you make sure that this will work?
Process
The first thing you have to decide is the scope of the temporary table. Local temporary tables (preceded by a single # character) are scoped to the session. Global temporary tables are available to all sessions. This can be important as it means that removal of a global temporary table is not a safe option. Because a local temporary table is specific to the creating session, it can be removed at will.
The next thing you should test is whether the temporary table already exists. You can use the T-SQL function OBJECT_ID for this purpose. Perhaps you always want to recreate the table so that it doesn't contain any spurious data. Here's an example:
IF OBJECT_ID (N'tempdb..#tempTableName') IS NOT NULL
DROP TABLE #tempTableName
Notice the use of the two periods '..'. This tells SQL Server that you are referring to no particular schema within the tempdb database. This is the database that contains temporary tables, and so is always the right one to use.
Once you've made sure the table doesn't already exist, you can create it as per usual. Here's an example for storing mapping information between old and new IDs across multiple tables:
IF (@@ERROR = 0)
BEGIN
CREATE TABLE #tempTableName (
TABLE_NAME varchar(255) NOT NULL,
SOURCE_ID int NOT NULL,
DESTINATION_ID int NOT NULL
)
END
Once the temporary table has been created, you can use it in any queries, cursors or SET statements as per any other table. Just remember to use the # or ## prefix (depending on whether it is a local or global temporary table, respectively).
Don't forget to delete the table if it is appropriate to do so at the end of your stored procedure.
Versions
Metadata