SQLite - TRUNCATE TABLE

The SQLite TRUNCATE TABLE statement is used to delete complete data from an existing table.

SQLite does not have an explicit TRUNCATE TABLE command like other databases. Instead, it has added a TRUNCATE optimizer to the DELETE statement. To truncate a table in SQLite, DELETE statement without a WHERE clause can be used. This will empty the table but the table structure will be retained.

The SQLite DROP TABLE statement can also be used to delete complete data of a table but it will delete whole table structure from the database.

Syntax

The syntax of using DELETE FROM statement is given below:

DELETE FROM table_name;

Example:

Consider a database containing a table called Employee with the following records:

The description of the table can be checked using pragma table_info statement as shown below:

pragma table_info('Employee');

This result of the above code will be:

To truncate this table, the statement is given below:

DELETE FROM Employee;

After truncating the table, the pragma table_info statement will still show the same structure as shown above but the table will contain no records.