Copyright

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 Speed

Recommended Lessons and Courses for You

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.

Expert Contributor
Christianlly Cena

Christianlly has taught college physics and facilitated laboratory courses. He has a master's degree in Physics and is pursuing his doctorate study.

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

Additional Activities

Modified True or False on SQL DROP Temp Table


Exercise

Check your knowledge in the lesson about SQL Drop Temporary Table by determining whether the following statements are true or false. For this activity, print or copy this page on a piece of paper. Write TRUE if the statement is valid and FALSE if otherwise on the blank space provided. If the statement is FALSE, write down the word or phrase that makes it wrong.

_____ 1. When a temporary table is no longer needed, then it is best to store it.

_____ 2. If one needs to create data for a long period of time and then eventually discard it, then it is advisable to use a temp table.

_____ 3. Removing a temp table is a means of keeping the database clean.

_____ 4. SQL tools provide the means of only creating temp tables.

_____ 5. It is best to check whether the table exists before deleting it, because this might cause errors in processing.

_____ 6. Removing a temporary table is an essential part of SQL database administration.

_____ 7. In an SQL server, the temporary tables all are prefixed with the ampersand symbol.

_____ 8. There is a special way to format the syntax to remove temp tables.

_____ 9. If you use the temporary table after the DROP TABLE command runs, the program will likely run smoothly.

_____ 10. DROP TABLE is a command used to delete or drop the temporary table and all of its data.

Answer Key

For False answers, the correct word or phrase is written in italic right next to the incorrect word for comparison.

1. FALSE, store, drop

2. FALSE, long, short

3. TRUE

4. FALSE, only creating temp tables, creating and removing temp tables

5. TRUE

6. TRUE

7. FALSE, ampersand, pound

8. TRUE

9. FALSE, run smoothly, crash

10. TRUE

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