560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This article is part of a complete SQL tutorial. Another way to learn SQL is to attend a Wise Owl SQL training course. |
There are two ways to ensure that a temporary table doesn't exist before you create 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!
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).
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.