Create temporary table in mysql stored procedure

You can create and use temporary tables in a stored procedure, but the temporary table exists only for the duration of the stored procedure that creates it. When the procedure completes, Adaptive Server automatically drops the temporary table. A single procedure can:

• Create a temporary table

• Insert, update, or delete data

• Run queries on the temporary table

• Call other procedures that reference the temporary table

Since the temporary table must exist to create procedures that reference it, here are the steps to follow:

1. Create the temporary table using a create table statement or a select into statement. For example:

   create table #tempstores
       (stor_id char(4), amount money)
   

2. Create the procedures that access the temporary table (but not the one that creates it).

   create procedure inv_amounts as
       select stor_id, "Total Due" = sum(amount) 
       from #tempstores
       group by stor_id
   

3. Drop the temporary table:

   drop table #tempstores
   

   This step is unnecessary if you use deferred_name_resolution.

4. Create the procedure that creates the table and calls the procedures created in step 2:

   create procedure inv_proc as
   create table #tempstores
   (stor_id char(4), amount money)
   

   When you run the inv_proc procedure, it creates the table, which only exists during the procedure’s execution. Try inserting values into the #tempstores table or running the inv_amounts procedure:

   insert #tempstores
   select stor_id, sum(qty*(100-discount)/100*price)
   from salesdetail, titles
   where salesdetail.title_id = titles.title_id
   group by stor_id, salesdetail.title_id
   exec inv_amounts
   

You cannot, because the #tempstores table no longer exists.

You can also create temporary tables without the # prefix, using create table tempdb..tablename... from inside a stored procedure. These tables do not disappear when the procedure completes, so they can be referenced by independent procedures. Follow the above steps to create these tables.

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.

The CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad. The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment. This is similarly a no-op if the temporary table isn't there:

   DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;

Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be. That's going to be essential for performance, either with this code or the existing code.

It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer. Note that the column declared must be the same as the name or alias of the column in the SELECT, otherwise MySQL assumes the SELECT is supposed to generate additional columns for the temporary table using the column names in the SELECT:

   CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
     ID INT NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY AS (
    SELECT ID FROM BATCH_ITEMS 
     WHERE STATUS = 'UNPROCESSED' 
       AND BATCH_JOB_ID = batchId 
     LIMIT size
   );

Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL. This is the other likely reason this procedure may be slower than you expect.

UPDATE BATCH_ITEMS BI
  JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
   SET BI.STATUS = 'PROCESSING';

Then return the result-set...

SELECT * FROM BATCH_ITEMS_UOW;

Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.

DROP TEMPORARY TABLE BATCH_ITEMS_UOW;

Create MySQL temporary table

Temporary Table in MySQL is an extremely useful and flexible feature that enables you to achieve complex tasks quickly. In this tutorial, we'll look at the advantages of using temporary tables as well as the limitations of their usage.

Note:
Don't confuse yourself with MySQL memory table type which is also temporary in nature because it stores data in computers memory only. In contrast, Temporary Table in MySQL can be created on disk or as a MEMORY table type, dependent on the syntax in the CREATE TABLE statement.

How to create MySQL Temporary table

To create a Temporary Table in MySQL, we add the TEMPORARY keyword in the CREATE TABLE statement.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

For example, the following statement creates a temporary table for products above average price.

create temporary table tempProductsAboveAvgPrice
as
select distinct ProductName, UnitPrice
from Products
where UnitPrice > (select avg(UnitPrice) from Products)
order by UnitPrice

Temporary Table can only be created as type MEMORY, MyISAM, MERGE, or InnoDB. If you don't specify an engine type when creating the Temporary Table, the default storage engine for the server is used. The default storage engine is set in MySQL server configuration file my.cnf.

MySQL manual states:

Since MySQL 5.5, InnoDB becomes the default storage engine and most users never changed the default settings, and it's also recommended by MySQL that the default setting should not be changed because of the ACID (Atomicity, Consistency, Isolation, Durability) benefits of InnoDB tables, e.g. transaction safe, referential integrity enforcement, and crash recovery.

Now the temporary table tempProductsAboveAvgPrice has been created, you can query it the same way as you do for a permanent table.

select * from tempProductsAboveAvgPrice

Here is the query result. 25 records returned.

You also can create the temporary table tempProductsAboveAvgPrice in 2 steps as shown below.

Step 1:

create temporary table tempProductsAboveAvgPrice
(
    ProductName varchar(40) not null,
    UnitPrice double not null
)

Step 2:

insert into tempProductsAboveAvgPrice
select distinct ProductName, UnitPrice
from Products
where UnitPrice > (select avg(UnitPrice) from Products)
order by UnitPrice

Now issue a SHOW TABLES command for Northwind database, the temporary table tempProductsAboveAvgPrice is not listed in the result. This is because the SHOW TABLES command only lists permanent tables in the database.

Here is the query result of SHOW TABLES command. 9 records returned.

Drop Temporary table

By default, all the temporary tables created are dropped automatically by MySQL when your database connection is terminated, but you still can manually drop a temporary table by issuing DROP TABLE command.

drop table tempProductsAboveAvgPrice

When to use MySQL Temporary table

The Divide and Conquer rules - this is the single most attractive advantage of using MySQL Temporary Table.

Temporary Table offers definite advantages when it's used to hold data returned from a very complex query (so very expensive to create) for temporary use. For example, you need to retrieve a bunch of data from multiple tables with join, union, group by, etc., then you need to do some work on the resulting dataset...

Therefore, if you ever find you frequently write complex SQL queries, consider using Temporary Tables to simplify your work. This also makes other people's life easier if they need to maintain your queries or customize for some other uses.

Advantages of using Temporary Table

1. The temporary table's dataset is needed in other queries because it can dramatically reduce the complexity of the entire work (e.g. you break a complex query into pieces of small queries with the use of Temporary Tables).
2. Immediate result set is needed because the result set is required more than once in different queries, therefore eliminates the need to run the query again to get the same data.
3. You need to manipulate (add unique IDs aka auto number, update, insert, or delete) the result of a query in a temporary place, without needing to create permanent tables in a production environment for data manipulation. This is very useful in situations where you need to repeatedly perform the same action in a database. Consider to prepare the data in a Temporary Table, do all the works upfront, and when the data is ready, add the data to some table in one go by using a single set-based query such as cross update query or bulk insert query. This happens quite often in real life. Suppose you need to move 10 bricks from point A to point B. Which one is more efficient - carry 1 brick each time from A to B for 10 times, or put the 10 bricks in a trolley and push the trolley from A to B in one go?

Limitations of using Temporary Table

As of MySQL version 5.7, MySQL TEMPORARY tables have the following limitations:

1. A Temporary Table is visible only to the current session, and is dropped automatically when the session is closed. In other words, the Temporary Table is automatically removed on closure of database connection. Note that you also can use the DROP TABLE statement to explicitly drop a Temporary Table.

   Tip:

   A Temporary Table is created inside the database that the session is connected to. If the Temporary Table you create has the same name as an existing permanent table, the existing table becomes hidden until the temporary table is dropped. For example, if you create a Temporary Table called customers in Northwind Traders database, the existing permanent table customers will become invisible for any queries. When you select data from the customers table, it retrieves data from the customers Temporary Table, not the existing permanent customers table. To minimize any confusion or potential problems (e.g. dropping a permanent table accidentally), it is recommended that you always prefix the name of a Temporary Table with tmp_ or temp_ in order to differentiate it from the existing permanent tables.

   The best way to drop a MySQL Temporary Table

   You can use the DROP TABLE statement to remove a temporary table. However it is good practice to use the DROP TEMPORARY TABLE statement instead because the DROP TEMPORARY TABLE statement removes only temporary tables, not the permanent tables. If you try to drop a permanent table with the DROP TEMPORARY TABLE statement, you will get an error message stating that the table you are trying to drop is unknown.

   For example, to remove the tempProductsAboveAvgPrice temporary table, you use the following statement:

   drop temporary table tempProductsAboveAvgPrice

   If you develop an application that uses a connection pooling or persistent connections, it is not guaranteed that the temporary tables are removed automatically when your application is terminated. This is because the database connection that the application is using may be still open and is placed in a connection pool for other clients to use. This means you should always explicitly remove any temporary tables that you have created in a session whenever you are done with them in that session.

   A database session is the conversation between an application (a client application or server application) with a relational database and all the objects which persist during the dialog. For example, after you have opened SQLyog or MySQL Bench (MySQL client tool which can interact with MySQL) and connect to your MySQL database, a database session is created and maintained until SQLyog is closed. Another example is an Apache web server opens a connection to MySQL database to select data via a PHP web page.

2. CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.
3. TEMPORARY tables have a very loose relationship with database schemas (e.g. Northwind database). Dropping a database does not automatically drop any TEMPORARY tables created within that database. Also, you can create a TEMPORARY table in a non-existent database if you qualify the table name with the database name in the CREATE TABLE statement. In this case, all subsequent references to the table must be qualified with the database name.

   The following query creates a temporary table in a fake database xxx.

   create temporary table xxx.tempProductsAboveAvgPrice
   as
   select distinct ProductName, UnitPrice
   from Products
   where UnitPrice > (select avg(UnitPrice) from Products)
   order by UnitPrice

   Then when you query the table, you also need to use xxx to qualify the table name.

   select * from xxx.tempProductsAboveAvgPrice

4. Temporary Table can only be created as type MEMORY, MyISAM, MERGE, or InnoDB.
5. You cannot refer to a Temporary Table more than once in the same query, or you'll get ERROR 1137: Can't reopen table: 'tempProductsAboveAvgPrice'.

   This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

   To solve this problem, create two Temporary Tables with different names from the same SELECT query.

6. The SHOW TABLES statement does not list Temporary Table.

7. You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead.

   ALTER TABLE tempProductsAboveAvgPrice RENAME tempProductsAboveAvgPrice2

8. The CREATE TEMPORARY TABLES privilege enables the creation of temporary tables using the CREATE TEMPORARY TABLE statement.

   The following is quoted from MySQL manual:

   After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.

   One implication of this behavior is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the CREATE TEMPORARY TABLES privilege but is able to execute a DEFINER-context stored procedure that executes with the privileges of a user who does have CREATE TEMPORARY TABLES and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it.

   To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation is to create a database dedicated to the use of temporary tables. Then for that database, a user can be granted the CREATE TEMPORARY TABLES privilege, along with any other privileges required for temporary table operations done by that user.

Other tutorials in this category

Back to Tutorial Index Page

Can we create temporary table in stored procedure in MySQL?

Can we create temporary table in stored procedure?

How do I create a temporary table from another table in MySQL?

Can we use temporary table in MySQL?