search
Search
Publish
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe: "Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
share
thumb_up_alt
bookmark
arrow_backShare
Twitter
Facebook

Creating a temporary table in MySQL

Database
chevron_right
MySQL
chevron_right
Cookbooks
chevron_right
Tables Cookbooks
chevron_right
General table Cookbook
schedule Mar 10, 2022
Last updated
local_offer MySQL
Tags

To create a temporary table in MySQL use the following syntax:

CREATE TEMPORARY TABLE table_name (column_name datatype);

To create a temporary table based on an existing permanent table:

CREATE TEMPORARY TABLE new_table SELECT * FROM existing_table LIMIT 0;

This will create a new temporary table with the same structure as the existing table, however, the temporary table will not be populated with records from the existing table due to the LIMIT 0 restriction (i.e. the temporary table will be created with zero records).

A few points to note regarding temporary tables:

    A temporary table is available only within the current session and is automatically dropped at the end of the session.

    To create a temporary table, you must have the CREATE TEMPORARY TABLES privilege.

    Temporary tables are often used when the results of a complex query are required as inputs for another query. As this allows us to break a single complex query into several smaller less complex queries, it greatly improves efficiency.

Examples

Creating temporary table from scratch

To create a temporary table called temp1 with one column shop_number that is of type INT:

CREATE TEMPORARY TABLE temp1 (shop_number INT NOT NULL);
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Creating temporary table based on existing table

Given an existing table drivers_license:

student_id

full_name

age

has_license

1

Sky Towner

17

0

2

Ben Davis

19

1

3

Travis Apple

18

0

4

Arthur David

16

0

5

Benjamin Town

17

1

The above sample table can be created using the code here.

To create a temporary table populated with data for student_id=1:

CREATE TEMPORARY TABLE temp_drivers_license
SELECT * FROM drivers_license WHERE student_id =1;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

To check the contents of the created temporary table temp_drivers_license:

SELECT * FROM temp_drivers_license;
+------------+------------+------+-------------+
| student_id | full_name | age | has_license |
+------------+------------+------+-------------+
| 1 | Sky Towner | 17 | 0 |
+------------+------------+------+-------------+
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...