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 one 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. |
You can use a variety of techniques to work with sets of records in SQL, but I always find myself coming back to temporary tables!
Suppose that you want to take a table of films (or movies for most of the world) and extract ones which won more than 5 Oscars for subsequent processing. Here's one way to do this:
-- write all films scoring more than
-- 5 Oscars into a new table
SELECT
FilmName,
FilmOscarWins
INTO
#GoodFilms
FROM
tblFilm
WHERE
FilmOscarWins > 5
-- now show all of the records in this
-- temporary table
SELECT * FROM #GoodFilms
ORDER BY FilmOscarWins DESC
The SQL code puts the records into a table with the name #GoodFilms. The # prefix shows that this is a temporary table.
A tempoary table is released at the end of the SQL which created it (in theory). In practice, they seem to hang around for a bit longer than this, and it's good practice to drop temporary tables first before recreating them (see later in this blog for how to do this).
The rest of this blog looks at temporary tables in more detail, showing where they are stored, how to drop them and the pros and cons of using them.
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.