Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

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.

  1. Temporary Tables in SQL Server - a Training Blog (this article)
  2. Where Temporary Tables are Stored - TEMPDB Database
  3. Dropping Temporary Tables - Object_Id or BEGIN TRY
  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 | no comments

Temporary Tables in SQL Server - a Training Blog

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! 

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

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.

  1. Temporary Tables in SQL Server - a Training Blog (this article)
  2. Where Temporary Tables are Stored - TEMPDB Database
  3. Dropping Temporary Tables - Object_Id or BEGIN TRY
  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.

Comments on this blog

This blog currently has no comments.

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.