Temporary Tables in SQL - a Training Blog
Part three of a four-part series of blogs

If you want training in how to use temporary tables in SQL, read on! This blog shows how to create temporary tables and how to drop them safely whether or not they exist. Finally, the blog lists the pros and cons of using temporary tables in SQL.

  1. Temporary Tables in SQL Server - a Training Blog
  2. Where Temporary Tables are Stored - TEMPDB Database
  3. Dropping Temporary Tables - Object_Id or BEGIN TRY (this blog)
  4. Pros and Cons of Temporary Tables

This article is part of a complete SQL tutorial.  Another way to learn SQL is to attend a Wise Owl SQL training course.

Posted by Andy Brown on 20 October 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Dropping Temporary Tables - Object_Id or BEGIN TRY

There are two ways to ensure that a temporary table doesn't exist before you create it:

  1. Try dropping it, and catch any error which occurs.
  2. Look for any table in the TEMPDB database with the right name, and if found, delete it.

Of the two methods, the second one is more difficult to understand but probably better: with the first method, you run the risk of trapping the wrong error!

 Method 1 - Drop and Catch

You can delete a temporary table using standard SQL syntax for TRY and CATCH:

-- try to delete temporary table

BEGIN TRY

DROP TABLE #BestMoviesEver

Print 'Deleted table'

END TRY

BEGIN CATCH

-- code will run if temporary table

-- wasn't deletable

PRINT 'No table to delete'

END CATCH

Here the PRINT statements are included just to show what happens when you run the SQL.  After deleting the temporary table, you could then try to create it:

-- don't display row count (clutters up window)

PRINT ''

SET NOCOUNT ON

-- create a temporary table containing movies

CREATE TABLE #BestMoviesEver(

MovieId int IDENTITY(1,1) PRIMARY KEY,

MovieName varchar(30),

Rating int

)

-- add in 4 movies

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'Watchmen', 9 )

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'The Departed', 8 )

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'The Matrix', 10 )

INSERT INTO #BestMoviesEver (MovieName, Rating) VALUES (

'The Sound of Music', 9 )

-- list them back out

SELECT * FROM #BestMoviesEver

Running this SQL twice would give the following output the second time:

Results of running query

Here we are running the SQL for a second time with the answers output to text. The PRINT statement has reported that we deleted the previous incarnation of the temporary table successfully (ie it must have already existed).

 

Method 2 - Use Object_Id 

A more complicated approach is to look in the system tables for a temporary table called #BestMoviesEver, and delete it if found:

-- delete temporary table if it exists

IF Object_id('tempdb.dbo.#BestMoviesEver', 'U') IS NOT NULL

BEGIN

PRINT 'Deleted table'

DROP TABLE #BestMoviesEver

END

If you want to know why the Object_id function works (and you probably don't!) run the following SQL statement:

SELECT * from tempdb.sys.objects

This will show a list of all SQL Server objects in the TEMPDB database:

Listing of objects in the TEMPDB database

Part of the listing of objects in the TEMPDB database

All of the objects with type U are user-defined tables! 

  1. Temporary Tables in SQL Server - a Training Blog
  2. Where Temporary Tables are Stored - TEMPDB Database
  3. Dropping Temporary Tables - Object_Id or BEGIN TRY (this blog)
  4. Pros and Cons of Temporary Tables
This blog has 0 threads Add post