How to Create and Use Temporary Tables in SQLite

1. Create a Temporary Table in SQLite
2. Select From a Temporary Table in SQLite
3. Drop a Temporary Table in SQLite
4. Insert, Update, and Delete Records in a Temporary Table in SQLite

Temporary tables can be incredibly useful in SQLite. However, they are known to be a bit tricky to use.

This article discusses the creation and use of temporary tables in depth in SQLite.

Create a Temporary Table in SQLite

Creating a temporary table in SQLite is much simpler than you think. It is pretty similar to creating a regular table. The code to create one is shown below.

First, you will have to create a regular table because a temporary table will need a reference to one already created.

CREATE TABLE users
(
    users_id INTEGER,
    name TEXT,
    age INTEGER
);

After this, you can create a temporary table that copies the user’s table above.

CREATE TEMPORARY TABLE table1
AS
(
SELECT * FROM users
);

This will create a temporary table identical to the user’s table. It is best to insert a few values before creating a temporary table so that you can verify that it is a copy of the latest table, and this can be done using the INSERT INTO clause.

You can always create a table using a more complex query. All you have to do is replace the SELECT query after the AS clause with a query of your choice.

Once you’re done, your temporary table will be created and ready for use. Note that TEMP can also replace the TEMPORARY clause, and both are accepted in SQLite.

Select From a Temporary Table in SQLite

Now we know how simple it is to create one, you might be wondering how you can select from it. It uses the usual SELECT clause identically, but it is best to opt for a slightly different approach than selecting a regular or permanent table.

It is best to add an additional alias using the AS clause for temporary tables. An example using the same user table and corresponding temporary table defined above is shown here:

SELECT 'table1'
AS from_table,* FROM table1

This would have worked if you also used SELECT * FROM table1. However, it is best to use this method so you can differentiate between your temporary table’s content from your permanent table.

This is especially helpful while dealing with tables with multiple entries. It is important to note that you can replace the asterisk symbol (*) with any query of your choice.

Use the With Clause

Another less common method of selection is the WITH clause. However, this can only be used if you create a query with only one select statement.

If you intend to use complex queries, it is best to stick with the method explained before. You can try out this method using the code below.

WITH table1(users_id, name,age) AS
(
   --add any one select statement
)
SELECT users_id, name,age FROM table1;

Other clauses such as set operators, grouping, or ordering are also often available for all temporary tables. They are used the same way as in permanent tables, as they will execute correctly as long as your query is written right.

Drop a Temporary Table in SQLite

In SQLite, dropping a temporary table is similar to dropping an ordinary or permanent table in your database. Temporary tables are often dropped automatically after the user ends a particular session, but you can always drop them manually if you like.

Remember that you should replicate any data you want to be saved into the permanent table before dropping it to avoid data loss. The following query shows how to drop a temporary table.

DROP TABLE temp.table1

The only difference here is that an additional clause is added before the name of the table. This signifies that the table to be dropped is a temporary table.

While dropping a temporary table, it is often best to add an IF EXISTS clause. This is because the table might have been dropped automatically without you knowing it.

Adding this prevents errors that can be difficult to identify in longer codes. Use the following code to drop a temporary table.

DROP TABLE If EXISTS temp.table1;

Insert, Update, and Delete Records in a Temporary Table in SQLite

Inserting, updating, and deleting records within a temporary table is pretty much the same as a permanent one. It is important to note that all data added and edited will be lost after the user exits the session or if it expires.

Here are a few examples you can refer to for all three of the above using the same temporary table created previously.

Insert Records in a Temporary Table

INSERT INTO users(users_id, name,age)
VALUES(1, 'abc', 30);

This will successfully insert the values into your temporary table. However, if you have unique constraints, you might want to add the IGNORE or REPLACE clauses to prevent errors while executing it.

Update Records in a Temporary Table

UPDATE users
SET name='xyz' WHERE users_id=3;

This will change the name of the user in the temporary table with the id 3. You can replace the content after the where clause with any condition necessary.

Delete Records in a Temporary Table

DELETE * FROM users

This query will delete all of the contents in the temporary table. If you only want to delete a specific row, you must include a WHERE clause such as the one below.

DELETE * FROM users
WHERE users_id=1

That is everything you need to know to start working with temporary tables. We hope this article has helped you get started and understand how to use them properly.