SQL DROP Temp Table: Tutorial & Explanation

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: SQL DROP Index & DROP Database: Tutorial

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 Temp Tables
  • 1:08 Dropping Tables
  • 2:31 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed
Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

A temporary table should only be temporary: when it's no longer needed, drop it. This lesson covers how to drop a temporary table in SQL, including syntax and examples.

Temp Tables

Before we start dropping temporary tables, let's do a quick recap of temporary tables, which we'll be calling temp tables, in SQL terms. Often we need to create a temp table: When we only need to create the data for a short period of time, and then discard it. A good example is the processing of an employee's payroll. We may need to create some temporary tables for calculations and then drop them after we've updated all of the standard tables.

Using temporary tables will save space in the database, because you don't have to take up space with information that will be discarded later. However, a temporary table really doesn't do much good if you just keep them laying around. Therefore, removing the temporary table is the means by which we keep the database clean.

The following code is a quick example of creating that temporary table. We're going to drop this later in the lesson, using SQL server:


DECLARE LOCAL TEMPORARY TABLE #employe_temp;
  employeeID int NOT NULL,
  empName char(50) NOT NULL,
  emp hourWorked double NOT NULL
);


Note that in an SQL server, the temporary tables all are prefixed with the pound symbol: #. Other systems don't have such a requirement; rather, you create the table using syntax similar to the prior example, and simply drop it using the DROP TABLE statement.

Dropping Tables

The DROP TABLE command is used to delete or drop the temporary table and all of its data. Remember that we named our table with the prefix #, for SQL server purposes.


DROP TABLE Table_Name;


Again, the example is from SQL server. In MySQL, you don't need the special prefix in front of the temporary table name.

This Deletes Data!

Using DROP TABLE deletes the table and all data! Even though this is your intent, it bears repeating, in case things get confused: this deletes data - the table and everything in it will be gone.

If you try to reference or use this table after the DROP TABLE command is run, the system will display errors, or your stored procedure/program will likely crash.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support