BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Temporary Tables in SQL Server - a Training Blog
- Where Temporary Tables are Stored - TEMPDB Database
- Dropping Temporary Tables - Object_Id or BEGIN TRY (this blog)
- 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:
- Try dropping it, and catch any error which occurs.
- 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:
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:
Part of the listing of objects in the TEMPDB database
All of the objects with type U are user-defined tables!
- Temporary Tables in SQL Server - a Training Blog
- Where Temporary Tables are Stored - TEMPDB Database
- Dropping Temporary Tables - Object_Id or BEGIN TRY (this blog)
- Pros and Cons of Temporary Tables