Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Create and Delete the SQLite Temporary Table – Querychat

Often it is impossible to fit long and complex operations into one query. Moreover, during such operations, intermediate data appears. This intermediate data is also often necessary to store, as we might need it later for further operations. Such data can be stored in a temporary table, and in this article, we will learn how to create and delete temporary tables in SQLite.

What is an SQLite temporary table

After we create a temporary table during a session, it exists until the end of the session. When the session is completed, the temporary table disappears. 

This is the syntax which can be used to create a temporary table:

CREATE TEMPORARY TABLE table_name(column_1 datatype, column_2 datatype, ...);

Where:

  • table_name is the name of the new temporary table.
  • column 1, column_2… are the names of temporary table columns.
  • datatype is assigned to every column.

Note: The names of our main table and the temporary one need to be different in order to be opened properly. The same names can cause the problem of the inability to open the main table. Our advice is to use the keyword indicating that the table is temporary when naming it.

Creating the SQLite temporary table

Let’s create a temporary table called “Songs” in our database. It will contain 4 columns, the “IdSong”, the “SongName”, the “Album”, and the “Singer”.

We will use the script: 

CREATE TEMPORARY TABLE Songs (IdSong INTEGER PRIMARY KEY, SongName TEXT, Album TEXT,Singer TEXT);

And now we will add one record to our table using this script:

INSERT INTO Songs VALUES (1, 'Birds', 'Origins', 'Imagine Dragons');

This is how our table looks like: 

SELECT * FROM Songs;

Result: 

Figure 1. “Songs” temporary table

If we finish the session, start a new one, then request our temporary table “Songs”, we will get a message stating that nothing was found.

Figure 2. The SQLite temporary table is missing

Deleting the temporary table

As explained, the temporary table disappears right after we finish our session and we cannot query the temp table after the session ends.

But sometimes we need to drop the table intentionally when the names of temporary and normal tables are the same and temporary table prevents from opening the normal one.

We can do this by using a simple DROP syntax:

DROP TABLE Songs;

Using the temp table 

Suppose we need to analyze car data and the clients who rent these cars by doing a join from two tables named RedCoolCars and ClientsCoolRedCar. After that, suppose we need to do some processing on the join results (such as string manipulations, aggregations, and so on), then export the result to an Excel file.

As there is an intermediate processing before we output the result to a file, we can use a temporary table to hold the data during this process. 

Let’s create two tables. The first will be “RedCoolCars“.

We will use the script: 

CREATE TABLE RedCoolCars (

                         RedCoolcarId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

                         Kind_of_Car NVARCHAR(30) NULL,

                         ClientID INTEGER NOT NULL,

                         FOREIGN KEY(ClientID) REFERENCES ClientsCoolRedCar(ClientId)

);

INSERT INTO RedCoolCars VALUES (1, 'Lamborghini', 10);

INSERT INTO RedCoolCars VALUES (2, 'Mazda', 5);

INSERT INTO RedCoolCars VALUES (3, 'Chevrolet', 3);

INSERT INTO RedCoolCars VALUES (4, 'Ford', 3);

INSERT INTO RedCoolCars VALUES (5, 'Dodge Charger', 2);

INSERT INTO RedCoolCars VALUES (6, 'BMW', 8);

INSERT INTO RedCoolCars VALUES (7, 'KIA', 7);

INSERT INTO RedCoolCars VALUES (8, 'Audi', 9);

INSERT INTO RedCoolCars VALUES (9, 'Mercedes', 5);

INSERT INTO RedCoolCars VALUES (10, 'Hyundai', 1);

INSERT INTO RedCoolCars VALUES (11, 'Hyundai', 10);

INSERT INTO RedCoolCars VALUES (12, 'Chevrolet', 4);

INSERT INTO RedCoolCars VALUES (13, 'Mazda', 7);

And the other table will be “ClientsCoolRedCars”: 

CREATE TABLE ClientsCoolRedCar (

                             ClientId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

                             ClientName NVARCHAR(50) NULL,

                             CliendPhone NVARCHAR(30) NULL

);

INSERT INTO ClientsCoolRedCar VALUES (1, 'Ann Smith', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (2, 'John Takke', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (3, 'Austin Reeves', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (4, 'Mike Johnson', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (5, 'Ana Wilson', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (6, 'Jane Miller', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (7, 'Ian Huckabee', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (8, 'Ann Brown', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (9, 'Matthew Davis', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (10, 'Dave Scott', 'xxxxxxxx');

And now we will create a temporary table, that will use the data from both of these tables: 

CREATE TEMPORARY TABLE tableTMP22 AS

SELECT

 Kind_of_Car,

 RedCoolcarId,

 ClientId,

 ClientName

FROM

 RedCoolCars

 LEFT JOIN ClientsCoolRedCar USING (ClientID)

ORDER BY

 ClientName;

Result:

Figure 3. The SQLite temporary table with JOIN operator

We can then apply any processing to the intermediate data we have in the tableTMP22 temporary table before we output the final result to an Excel file.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc