How to create temporary tables in SQL

Once connected to an SQL server, you have the option of creating at least a non-temporary (standard) table, temporary table, and memory table. An example non-temporary table is created as follows:

create table tableName(intColName int(20));

This will behave as a non-temporary table depending on the global or session storage engine. The session (or default) storage engine can be set with:

–default-storage-engine

at startup, or:

set session storage_engine = engine;

or

set storage_engine = engine;

during runtime, where engine is the storage engine you want to set. An example temporary table is created with:

create temporary table tableName (IntColName int(20));

Temporary tables are different from standard tables because they only exist for the duration of the client connection to the server. When this connection ends, the temporary table is destroyed. The client that created the temporary table is the only table that can access it. Any non-temporary table that is already existing and shares the same name will not be accessible by the temporary table.

A memory table is a third classification of tables. They are created with:

create table tableName(IntColName int(20) engine = memory;

This is a table where the storage engine is of the type memory. Memory tables are unique in how they have a persistent datastore, but the contents of their table is wiped when the server is restarted. The signature of the memory table will remain defined after each reboot, but its contents will have been emptied. They are different from temporary tables in how they can be accessed from any client, not just the one that created it.