Create Temporary Table in MySQL

by

In this article we are going to discuss about how to create temporary table in MySQL.

MySQL has a property to create a temporary table where we can keep keep temporary data.MySQL can also delete this table automatically whenever current session is ended or the user stop the program. WE can also remove it manualy using Drop method.

In MySQL we can create temporary table using ‘Temporary’ keyword.

Create temporary table and insert data

Here we are going to create a temporary table and insert some data in it.

Syntax:

CREATE TEMPORARY TABLE temporary_table_name SELECT * FROM existing_table_name LIMIT 0;

So suppose we have an existing table ‘registration_details’,

MySQL create table_existing
Now we are going to create a new table with the help of our existing table ‘registration_details’ below is our query for this,

CREATE TEMPORARY TABLE registered_employee
SELECT first_name, desig_post FROM registration_details WHERE desig_post ="Developer"  AND last_name="Mishra" ;

Now by using below select query we will see our output,
select * from registered_employee;

MySQL create table_2
So you can see that we have got our output registered_employee having only those employee who have last_name =’mishra’ and desig_post=’developer’ as we have given condition.

Now we are going to add some data using insert query,let’s see how it will work

INSERT INTO registered_employee(first_name,desig_post)
SELECT first_name,desig_post
FROM registration_details
WHERE
desig_post="tester"
;

Here is our output:

MySQL insert table_3
In above query you can see that we have given condition desig_post=”tester” so it added those name having tester designation.

Create temporary table in a select statement without a separate create table

Now we are going to create a table using ‘AS’ keyword,below is the query for it.

CREATE TEMPORARY TABLE IF NOT EXISTS registered_employee AS 
 (SELECT first_name,desig_post  FROM registration_details 
 WHERE desig_post ="developer" & "tester");

Here is output:

MySQL insert table_3

Create a temporary table in MySQL with an index

Now we are going to create a temporary table with an index,

Syntax:

CREATE TEMPORARY TABLE temporary_table_name (index_column_name INTEGER NOT NULL AUTO_INCREMENT, PRIMARY
KEY(index_column_name), INDEX(index_column_name))
SELECT * FROM existing_table_name
WHERE  ;

Now let’s take an example to understand this,

CREATE TEMPORARY TABLE registered_employee_with_index (registration_number INTEGER NOT NULL AUTO_INCREMENT, PRIMARY    
KEY(registration_number), INDEX(registration_number))
SELECT * FROM registration_details
WHERE desig_post ="developer" ;

Here is our output:

MySQL create table_4
So you can see that above we have given condition that ‘WHERE desig_post =”developer”‘ and we got our new table “registered_employee_with_index” with index number.

Add a column to temporary table in MySQL

Here we are going to add a new column using Alter command in our temporary table,

Syntax:

ALTER TABLE temporary_table_name ADD new_column_name  
DEFAULT default_value;

Let’s take an example,

We will be use above table registered_employee_with_index and adding a new column state_name to it.

ALTER TABLE registered_employee_with_index ADD state_name varchar(255) 
DEFAULT 'Andhra Pradesh';

MySQL create table_5
You can see in the above output, a new column state_name got added to the table registered_employee_with_index.

Conclusion:

In this article we have discussed  about how to create temporary table in MySQL using different method.Thank you!