This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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!

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Dropping Temporary Tables - Object_Id or BEGIN TRY

Method 1 - Drop and Catch

Method 2 - Use Object_Id

Head office

London

Manchester