MariaDB Temporary Table + Examples

In this MariaDB tutorial, we learn about the concept of MariaDB Temporary Table. Here we will discuss various examples related to the temporary tables in MariaDB. Additionally, we will discuss the following topics.

  • MariaDB temporary table
  • MariaDB temporary table in memory
  • MariaDB temporary table in stored procedure
  • MariaDB temporary table example
  • MariaDB temporary table from select
  • MariaDB temporary table lifetime
  • MariaDB create temporary table as select
  • MariaDB add index to temporary table
  • MariaDB check if temporary table exists
  • MariaDB drop temporary table if exists
  • MariaDB delete temporary table
  • MariaDB create temporary table if not exists
  • MariaDB grant create temporary table
  • MariaDB select into temporary table
  • MariaDB temporary table primary key
  • MariaDB create temporary table like
  • MariaDB create or replace temporary table
  • MariaDB update temporary table

MariaDB Temporary Table

In MariaDB, a temporary table is a special type of database table that enables us to store resultset in temporary form. Moreover, we can also utilize the temporary result multiple times within the same session. However, once the current sessions end, the temporary table also gets deleted automatically.

A temporary table in MariaDB is useful in various scenarios where we need to hold the instant results and also process them at the same time. In MariaDB, the client who generates the temporary table is the only one who has access to it.

However, we cannot create multiple temporary tables with the same name within the same session.

Creating temporary tables in MariaDB is quite similar to creating a standard table. For this, we just need to use CREATE TEMPORARY TABLE statement instead of CREATE TABLE. Here is the complete syntax of creating a temporary table in MariaDB.

CREATE TEMPORARY TABLE tbl_name(
   col_1,
   col_2,
   ...,
   tbl_constraints
);

In the above syntax, first, we are using the CREATE TEMPORARY TABLE statement where we need to specify the name of the temp table in place of tbl_name. After this, we can easily define the column definition just like we define while creating a MariaDB table.

Also, check: MariaDB Reset Root Password

MariaDB Grant Create Temporary Table

Before we start using the syntax, we should understand that a non-admin user cannot directly create temporary tables.

In MariaDB, a non-admin user will require to have a CREATE TEMPORARY TABLE privilege on a database before successfully creating temporary tables within it. Here is the SQL query to grant this privilege to a user.

GRANT CREATE TEMPORARY TABLES ON database_name TO 'username';

In the syntax, the GRANT statement is utilized to grant the CREATE TEMPORARY TABLE privilege on database_name to the username. Here we just need to specify the actual database name in place of the database_name keyword and also the name of the user in place of the username.

Now, let’s execute an example using the above syntax.

CREATE USER 'James'@'localhost';

GRANT CREATE TEMPORARY TABLES ON `mariadbtips`.* TO 'James'@'localhost';

In the above example, first, we are creating a new user with the name James. After this, we are using the GRANT statement to grant the CREATE TEMPORARY TABLE privilege on the mariadbtips database to user James.

Read: How to Grant User Access to a MariaDB Database

MariaDB Temporary Table Example

Now that we either have an administrative or a non-admin user with the right privileges, we are ready to create a temporary table in a MariaDB database. So, in this section, we will use the syntax from the first section to create a temporary table.

Here is the complete SQL query to create a temporary table in the mariadbtips database.

CREATE TEMPORARY TABLE tmp_usa_states
(
state_id  INT,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2)
);
 
INSERT INTO tmp_usa_states
VALUES (101, 'Alabama', 'AL'),
(102, 'Alaska', 'AK'),
(103, 'Arizona', 'AZ'),
(104, 'California', 'CA'),
(105, 'Florida', 'FL'),
(106, 'Georgia', 'GA'),
(107, 'Montana', 'MT'),
(108, 'New Jersey', 'NJ'),
(109, 'New York', 'NY'),
(110, 'Texas', 'TX');

In the above example, first, we are using the CREATE TEMPORARY TABLE statement to create a temporary table named tmp_usa_states. Now, in this table, we have specified 3 columns.

  • The first is the state_id column which is of INT data type.
  • After this, there is the state_name column which is of VARCHAR(50) data type.
  • In the last, we have added the state_code column which is of VARCHAR(2) data type.

After defining the temporary table, we are using the INSERT statement to insert 10 rows in the tmp_usa_states table. Now, once the temporary table is been created successfully, we can use the SELECT statement to fetch all the data of the table.

SELECT * FROM tmp_usa_states

Here is the result of the above query.

MariaDB Temporary Table
MariaDB Temporary Table

Read: How to create a user in MariaDB 

MariaDB Temporary Table From Select

In this section, we will understand how to create a temporary table in the MariaDB database using a SELECT statement.

A SELECT statement in any database management system allows fetching specific columns and their data. And by using the SELECT statement with CREATE TEMPORARY TABLE statement, we can copy table structure and data to form a new temporary table.

Here is the standard syntax that we can use for this implementation.

CREATE TEMPORARY TABLE temp_table 
( SELECT col_1, col_2, ...,
  FROM another_table );

In the above syntax, we are using the CREATE TEMPORARY TABLE statement to create a temp table in MariaDB with a certain name. After this, instead of specifying table columns, we are using the SELECT statement to fetch some specific columns and their data.

When we execute the statement, a temporary table will be created that will hold specified data from the SELECT statement.

Example-1

Let’s understand the use of the above syntax with the help of examples. And for the first example, we will create a temporary table by selecting all the columns using the SELECT statement. For the table, we will use the usa_states table that consists of data related to various states in the United States of America.

The complete example is as follows.

CREATE TEMPORARY TABLE tmp_usa_states  #Creating temp table
( SELECT * FROM usa_states );

In the above example, we have created a temporary table in MariaDB named tmp_usa_states. For the columns, we are using a SELECT statement that will fetch all the columns from the usa_states table.

Now, if we query the above created temporary table, we will get the following result.

MariaDB Temporary Table From Select
MariaDB Temporary Table From Select

Example-2

Next, let’s take another example where we will use the SELECT statement to fetch only a few columns from a table. For this, first, we will create a new table using the following SQL code.

CREATE TABLE Department_tbl(  
  dept_no INT AUTO_INCREMENT PRIMARY KEY,  
  dept_name VARCHAR(30),  
  dept_location VARCHAR(30)
);

INSERT INTO Department_tbl(dept_name, dept_location) VALUES('RESEARCH','DALLAS');
INSERT INTO Department_tbl(dept_name, dept_location) VALUES('OPERATIONS','BOSTON');
INSERT INTO Department_tbl(dept_name, dept_location) VALUES('SALES','New York');
INSERT INTO Department_tbl(dept_name, dept_location) VALUES('ACCOUNTING','CHICAGO');
INSERT INTO Department_tbl(dept_name, dept_location) VALUES('Human Resource Management','Phoenix');

In the above query, we have created a department table with 3 columns. And we have also inserted columns rows in the table. Here is the complete table.

MariaDB Temporary Table From Select Example
Creating a table in MariaDB

Now, we will use this table to create a temporary table with 2 columns. Here is the SQL query for this implementation.

CREATE TEMPORARY TABLE tmp_dept  #Creating temp table
( SELECT dept_no, dept_location FROM department_tbl );

In the above example, we are creating a temporary table in MariaDB using the data from the department_tbl. This time instead of using an asterisk (*) to fetch all the columns, we have specified 2 column names.

Now, if we query the temporary table, we will get the following result.

MariaDB create temporary table as select
MariaDB create temporary table as select

Read: MariaDB Backup Database

MariaDB Temporary Table Primary Key

In this section, we will understand how to create a temporary table with a primary key column in the MariaDB database.

Yes, just like a standard MariaDB table, we can create a temporary table with a primary key constraint. The key difference between a standard table and a temporary table is that the scope of a temp table is restricted to a session. Once the session ends, the temporary table in MariaDB will also come to an end.

Let’s look at the syntax of creating a temp table in MariaDB with a primary key.

CREATE TEMPORARY TABLE tbl_name(    #for 1 primary key column
   col_pk type PRIMARY KEY,
   col_2 type,
   ...
);

CREATE TEMPORARY TABLE tbl_name(    #for multiple primary key columns
   col_1_pk type,
   col_2_pk type,
   ...,
   PRIMARY KEY(col_1_pk,col_2_pk,)
);

So, just like creating primary key(s) for a standard MariaDB table, in the same way, we can create a temporary table with one or more primary keys. Now, let’s use the above syntax to implement an example in MariaDB.

CREATE TEMPORARY TABLE temp_dept_tbl(  
  dept_no INT AUTO_INCREMENT PRIMARY KEY,  
  dept_name VARCHAR(30),  
  dept_location VARCHAR(30)
);

INSERT INTO temp_dept_tbl(dept_name, dept_location) VALUES('RESEARCH','DALLAS');
INSERT INTO temp_dept_tbl(dept_name, dept_location) VALUES('OPERATIONS','BOSTON');
INSERT INTO temp_dept_tbl(dept_name, dept_location) VALUES('SALES','NEW YORK');

In the above example, we are creating a temporary table named temp_dept_tbl with 3 columns. Out of these 3 columns, we have defined dept_no as AUTO_INCREMENT PRIMARY KEY column. At the last, we are using the INSERT statement to insert 3 records in the temporary table.

Here is the final result when we query the temp table.

MariaDB Temporary Table Primary Key
MariaDB Temporary Table Primary Key

Read: MariaDB Truncate Table

MariaDB Add Index to Temporary Table

In this section, we will illustrate a method to add an index to a MariaDB temporary table. Adding an index to a table helps in improving the overall query execution. And same is the case with the temporary tables in MariaDB.

For more details related to the index, please refer to the “MariaDB index with Examples” tutorial. However, in this section, we will focus on creating any type of index to a temporary table. Here is the syntax of adding an index to a temporary table in MariaDB.

CREATE INDEX index_name
on tmp_table_name(col_list);

To add an index to a temporary table column, we can use the CREATE INDEX statement. In the syntax, we need to specify the name of the index in place of index_name. And also specify the table and columns in place of tmp_table_name and col_list respectively.

Now, let’s use this syntax to add an index to the following created temp table.

CREATE TEMPORARY TABLE tmp_usa_states
(
state_id  INT PRIMARY KEY,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2)
);
 
INSERT INTO tmp_usa_states
VALUES (101, 'Alabama', 'AL'),
(102, 'Alaska', 'AK'),
(103, 'Arizona', 'AZ'),
(104, 'California', 'CA'),
(105, 'Florida', 'FL'),
(106, 'Georgia', 'GA'),
(107, 'Montana', 'MT'),
(108, 'New Jersey', 'NJ'),
(109, 'New York', 'NY'),
(110, 'Texas', 'TX');

In the above example, we are using the CREATE TEMPORARY TABLE statement to create a temporary table named tmp_usa_states. This table will consist of 3 columns starting from state_id, state_name, and state_code.

After creating the above table, we will use the following SQL code to add an index on the tmp_usa_states table.

CREATE INDEX state_name_idx
ON tmp_usa_states(state_name);

In the above query, we have created an index name state_name_idx on the state_name column of the tmp_usa_states table. Now, we can use the EXPLAIN statement with SELECT query to check if an index is added or not.

MariaDB Add Index to Temporary Table
MariaDB Add Index to Temporary Table

Read: MariaDB Rename Column

MariaDB Delete Temporary Table

Till now, we have seen different examples related to how to create a temporary table in MariaDB. Now, let’s understand how to delete or drop a temporary table in MariaDB.

However, deleting temporary tables in MariaDB is not compulsory as a temp table gets automatically deleted when the session in which it was created ends. Still, we can use the following DROP command to delete a MariaDB temp table.

DROP TEMPORARY TABLE temp_table_name

In the above syntax, we simply need to specify the name of the temporary table in place of temp_table_name. And the specified temp table will be removed from the database.

Here is a simple example of deleting a temporary table in MariaDB.

DROP TEMPORARY TABLE tmp_usa_states;

In the example, we are deleting the temporary table named tmp_usa_states. And again if we try to use this table, the server will return the Table does not exist error message.

MariaDB Delete Temporary Table
MariaDB Delete Temporary Table

Read: MariaDB Order By Clause

MariaDB Drop Temporary Table If Exists

From the previous section, we get to know that if we try to drop a non-existing temporary table from MariaDB, the instance we return an error. So, in this section, we will understand how to handle the error message by using the DROP TEMPORARY TABLE IF EXIST statement.

Here is the syntax for the DROP TEMPORARY TABLE IF EXIST statement in MariaDB.

DROP TEMPORARY TABLE IF EXISTS
tbl_1, tbl_2, ...

When we use the IF EXISTS option with the DROP TEMPORARY TABLE statement, it will not raise the error message even if that table does not exist. Let’s understand this by using an example in MariaDB. And the example is as follows.

DROP TEMPORARY TABLE IF EXISTS tmp_usa_states;

In the above example, we are again trying to drop the same tmp_usa_states table that we deleted in the previous section. Now, when we execute this statement, the server will not return any error.

Note: When we use the IF EXISTS with DROP TABLE, it might show a warning at first instance if the table does not exist. However, we can ignore the warning by unchecking the warning box.

MariaDB Drop Temporary Table If Exists
MariaDB Drop Temporary Table If Exists

Read: MariaDB Union Operator

MariaDB CREATE OR REPLACE Temporary Table

In this section, we will understand how to use the CREATE OR REPLACE option while creating a temporary table in MariaDB.

Here is the complete syntax of using CREATE OR REPLACE statement while creating a MariaDB temporary table.

CREATE OR REPLACE TEMPORARY TABLE tbl_name(
   col_1,
   col_2,
   ...,
   tbl_constraints
);
  • When we use the CREATE TEMPORARY TABLE statement with OR REPLACE clause, the server will check if the temp table with the same already exists or not.
  • If the temp table with the specified name does not exist, then it will utilize the CREATE statement and create a new temporary table.
  • However, if the temp table already exists, it will drop the existing temporary table and create a new one with given name and columns.

Let’s understand the whole implementation using an example. For this first, we will create a temporary table by only using CREATE TEMPORARY TABLE statement. Here is the SQL query for this task.

CREATE TEMPORARY TABLE tmp_states
(
state_id  INT PRIMARY KEY,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2),
country_name VARCHAR(50) NOT NULL
);

In the above example, we created a temporary table named tmp_states with 4 table columns. Next, we will use the CREATE OR REPLACE clause to create a temporary table with the same name. But, this time, we will create 5 columns. Here is the SQL query for this task.

CREATE OR REPLACE TEMPORARY TABLE tmp_states
(
state_id  INT PRIMARY KEY,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2),
country_name VARCHAR(50) NOT NULL,
country_code VARCHAR(3)
);

Now, if we execute the above SQL script, it will replace the tmp_states table of 4 columns with the new 5 columns. And we can check the table structure using the DESC statement.

MariaDB CREATE OR REPLACE Temporary Table
MariaDB CREATE OR REPLACE Temporary Table

Read: MariaDB Select Into

MariaDB CREATE Temporary Table Like

In this section, we will discuss how to use the LIKE operator in MariaDB while creating a temporary table.

The main goal of using the LIKE operator with CREATE TEMPORARY TABLE statement is to create empty temporary using the table structure of some other table. With this, the entire table definition of a table can be used to create an empty temp table. Here is the complete syntax of this implementation.

CREATE TEMPORARY TABLE tmp_table_name LIKE another_table;

In the above syntax, we simply need to specify the name for the new temporary table in place of the tmp_table_name keyword. And also specify the name of another that we want to use in place of another_table keyword.

Let’s understand the whole implementation by executing an example in MariaDB. And here is the SQL code for the example.

CREATE TEMPORARY TABLE tmp_country LIKE country;

In the above example, we are creating a temporary table in MariaDB named tmp_country by using the table definition of the country table. Moreover, after execution, we can use the DESC table statement to check the table definition for the tmp_country table.

MariaDB CREATE Temporary Table Like
MariaDB CREATE Temporary Table Like

Read: MariaDB DateTime Tutorial

MariaDB SELECT INTO Temporary Table

In MariaDB, the SELECT INTO statement is generally utilized to store some column values from a table into some variables. Moreover, we have to utilize this statement in such a way that the query returns a single row, else the MariaDB will return some error or warning.

For more detail related to MariaDB SELECT INRO statement, please refer to the MariaDB Select Into + Examples tutorial.

The main goal of using this statement is to copy data from a table into a variable. However, we cannot use this statement to copy data into temporary tables as it only works with variables.

But, in the case of temporary tables, we can use the SELECT statement to not only copy table definition but also the data from another table. We have already explained his implementation in the “MariaDB Temporary Table From Select” subtopic.

MariaDB CREATE Temporary Table IF NOT EXISTS

Generally, when we are working with a large set of tables, it is difficult for a DBA to remember all table names and each time use a unique table name. However, in MariaDB, we cannot create multiple tables with the same name in a database. Because in that case, the instance will return an error.

And same is the case with MariaDB temporary tables, we cannot create 2 temp tables with the same session. However, even if try to create it, the MariaDB will return an error.

MariaDB CREATE Temporary Table without IF NOT EXISTS
MariaDB CREATE Temporary Table without IF NOT EXISTS

In the above example, we are trying to create a temporary table with the name “tmp_country” that already exists. So, the MariaDB will return an error message.

Now, to handle the above error, we can utilize the IF NOT EXISTS clause. By using this clause, the temporary table will only be created when any temporary table with a specified name does not exist already. However, if a temporary table with the same name already exists, the MariaDB will return a warning.

Here is the syntax of using CREATE TEMPORARY TABLE IF NOT EXISTS statement in MariaDB.

CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name(
   col_1,
   col_2,
   ...,
   tbl_constraints
);

Now, let’s understand the implementation using an example. And the query for the example is given below.

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_country 
LIKE country;

In the above example, we are using the CREATE TEMPORARY TABLE statement IF EXISTS clause to create a temp table name tmp_country. Now, if the temp table with this name does not exist, it will create the table successfully. However, if the temp table with the specified name already exists, the instance will return a warning.

MariaDB CREATE Temporary Table IF NOT EXISTS
MariaDB CREATE Temporary Table IF NOT EXISTS

Read: MariaDB Primary Key 

MariaDB Update Temporary Table

In this section, we will understand how to update a MariaDB temporary table. Moreover, here we will illustrate the syntax and example for this implementation.

So, in MariaDB, the UPDATE statement is utilized to modify the table data. However, we can also use this statement on temporary tables as well. Here is the complete syntax of using the MariaDB UPDATE statement.

UPDATE tmp_tbl_name
SET col1 = some_value,
    col2 = another_value,
    ...
[WHERE condition];

In the above syntax, first, we are using the UPDATE table statement but here we have to specify the name of the temporary table. After this, we are using the SET clause to specify certain values for some columns. Moreover, we can also utilize the WHERE clause to specify a certain condition. But still, the WHERE clause is optional to use.

Now, let’s use the above syntax on a temporary table in MariaDB. And the example is as follows.

CREATE TEMPORARY TABLE tmp_usa_states
(
state_id  INT,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2)
);
 
INSERT INTO tmp_usa_states
VALUES (102, 'Alaska', 'AK'),
(103, 'Arizona', 'AZ'),
(104, 'California', 'CA'),
(105, 'Florida', 'FL'),
(106, 'Georgia', 'GA'),
(109, 'New', 'NY');

In the above SQL script, first, we are creating a temporary table named tmp_usa_states. And we have also inserted some rows in this temp table. But, instead of specifying New York, we have only specified “New” in the column value.

Update Temporary Table in MariaDB
Update Temporary Table in MariaDB

Now, we will use the UPDATE statement with a temporary table to correct the above mistake. Here is the query for this implementation.

UPDATE tmp_usa_states 
SET state_name = 'New York' 
WHERE state_code='NY';

In the above query, we are modifying the value of the state_name column from the tmp_usa_states table where the state_code is ‘NY’. After executing the above statement, we can observe that the value for the stat_name column is modified.

MariaDB Update Temporary Table
MariaDB Update Temporary Table

Read: MariaDB Delete Row

MariaDB Temporary Table in Stored Procedure

In this section, we will discuss how to create and use a MariaDB temporary table within a stored procedure.

A store procedure in MariaDB is nothing but a group of prepared SQL statements that are stored within a database with a specific name. And whenever we call the name of a stored procedure in MariaDB, the prepared SQL script gets executed. For detail related to stored procedures in MariaDB, please refer to the MariaDB create procedure tutorial.

We will understand how to create a stored procedure in MariaDB for the temporary table using an example. And the query for the example is as follows.

DELIMITER $$
CREATE PROCEDURE sp_InsertStateNames
( IN stateName VARCHAR(50),
  IN countryName VARCHAR(50) )
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_state 
   ( state_id INT AUTO_INCREMENT PRIMARY KEY,
     state_name VARCHAR(50) NOT NULL,
     country_name VARCHAR(50)
 );

  INSERT INTO tmp_state(state_name, country_name)
  VALUES(stateName, countryName);
   
  SELECT * FROM tmp_state;
END $$
DELIMITER ;
  • In the above query, first, we are creating a stored procedure in MariaDB named sp_InsertStateNames with 2 parameters.
  • Within the procedure, we are using the CREATE TEMPORARY TABLE statement to create a temp table named tmp_state and that too with 3 columns.
  • After this, we are using the value of input parameters to insert one row in the temp table.
  • In the last, we are using the SELECT statement to fetch data from the temp table.

Now, when we call the above-created store procedure in MariaDB with some parameter values, it will return a temp table. Here is the query to call this procedure.

CALL sp_InsertStateNames('New York', 'United States');

In the above query, we are calling the sp_InsertStateNames stored procedure and we have also specified 2 parameters. The first is the state_name as “New York” and the second is country_name as the “United States“. In the end, we will get the following result.

MariaDB Temporary Table in Stored Procedure
MariaDB Temporary Table in Stored Procedure

Read: MariaDB Substring [11 Examples]

MariaDB Temporary Table Lifetime

A MariaDB temporary table is a special type of database table that enables us to store resultset in temporary form. Moreover, we can also utilize the temporary result multiple times within the same session. However, once the current sessions end, the temporary table also gets deleted automatically.

So, we can conclude that the life of a temporary table in MariaDB is limited to the current session. Once the session in which the temporary table is created ends, the life of the temporary table also ends. And it will be automatically deleted.

MariaDB Temporary Table in Memory

In this section, we will understand how to create temporary tables in MariaDB with the Memory storage engine.

So, when we create a temporary table in MariaDB without specifying any storage engine, it is created by default in the disk. We can utilize the Memory storage engine in MariaDB to store temporary tables in memory instead of disk.

Here is the syntax of creating a temporary table in MariaDB in the memory storage engine.

CREATE TEMPORARY TABLE tbl_name(
   col_1,
   col_2,
   ...,
   tbl_constraints
) ENGINE=MEMORY;

In the above syntax, after defining the temporary table, we simply need to specify the engine name as MEMORY. Let’s understand the concept using an example in MariaDB.

CREATE TEMPORARY TABLE tmptbl__memory 
( state_id INT AUTO_INCREMENT PRIMARY KEY,
   state_name VARCHAR(50) NOT NULL,
   country_name VARCHAR(50)
) ENGINE=MEMORY;

In the example, we are creating a temporary table named tmptbl__memory with 3 columns. In the last, we have specified the ENGINE value as MEMORY.

Once the temp table is created, we can utilize it just like we were doing in our previous sections.

Read: MariaDB LIMIT + Examples

MariaDB check if temporary table exists

In this section, we will understand how to check if a MariaDB Temporary table already exists or not. And for this task, we are going to create a stored procedure in MariaDB that will return a statement based upon the existence of the temp table.

DELIMITER //
CREATE PROCEDURE check_tmptable_existence(tmp_tbl_name VARCHAR(100)) 
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @msg = 1;
    SET @msg = 0;
    SET @tbl_name = tmp_tbl_name;
    SET @query = CONCAT('SELECT 1 FROM ',@tbl_name);
    PREPARE sql_stmt FROM @query;
    IF (@msg = 1) THEN
        SET @msg_1 = 'Temporary Table does not exists';
        SELECT @msg_1 AS 'Result';
    ELSE
        SET @msg_1 = 'Temporary Table already exists';
        SELECT @msg_1 AS 'Result';
        DEALLOCATE PREPARE sql_stmt;
    END IF;
END //
DELIMITER ;

In the above procedure, first, we are passing the name of the temporary table as a parameter value. After this, we are preparing a SELECT SQL statement on the given table name. And if the statement returns an error then, it means that the table does not. However, if the statement gets executed, it will return a statement stating table already exists.

Now, let’s call the above procedure by passing a temporary table name.

CALL check_tmptable_existence('tmptbl__memory');

As we know, we have created this temp table named tmptbl__memory in the previous section. So, the procedure will return “Temporary Table already exists“. Here is the actual output.

MariaDB check if temporary table exists
MariaDB check if temporary table exists

You may also like to read the following MariaDB tutorials.

So, in this MariaDB tutorial, we have learned about the concept of MariaDB Temporary Table. Here we have discussed various examples related to the temporary tables in MariaDB. Additionally, we have covered the following topics.

  • MariaDB temporary table
  • MariaDB temporary table in memory
  • MariaDB temporary table in stored procedure
  • MariaDB temporary table example
  • MariaDB temporary table from select
  • MariaDB temporary table lifetime
  • MariaDB create temporary table as select
  • MariaDB add index to temporary table
  • MariaDB check if temporary table exists
  • MariaDB drop temporary table if exists
  • MariaDB delete temporary table
  • MariaDB create temporary table if not exists
  • MariaDB grant create temporary table
  • MariaDB select into temporary table
  • MariaDB temporary table primary key
  • MariaDB create temporary table like
  • MariaDB create or replace temporary table
  • MariaDB update temporary table