Remove All Rows From A Table With TRUNCATE

Delete every row in a table quickly with TRUNCATE TABLE, which is faster than DELETE with no WHERE clause.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Remove all rows from a staging table, then verify it is empty

After truncating, the staging table contains zero rows. The follow-up SELECT COUNT(*) confirms this. In SQLite the equivalent is DELETE FROM staging with no WHERE clause. For tables with foreign key references, some engines require disabling foreign key checks or truncating in dependency order before truncating the referenced table.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE staging (id INT, VALUE VARCHAR(50));

INSERT INTO
  staging (id, VALUE)
VALUES
  (1, 'pending'),
  (2, 'pending'),
  (3, 'pending');

Validated query Shared across supported engines.

SQL

TRUNCATE TABLE staging;

SELECT
  COUNT(*) AS row_count
FROM
  staging;

Expected result Returned rows for the shared example.

row_count
0

SQLite uses DELETE FROM instead of TRUNCATE. All engines produce the same result: an empty table with row_count = 0.

Useful when

Where this command helps.

clearing a staging or temporary table before reloading it
resetting test data between runs

Explanation

What the command is doing.

TRUNCATE TABLE removes all rows from a table without logging individual row deletions, making it significantly faster than DELETE FROM table on large tables. MySQL, MariaDB, SQL Server, and PostgreSQL all support the standard TRUNCATE TABLE syntax. SQLite does not implement TRUNCATE — use DELETE FROM table instead, which behaves identically in terms of result but may be slower. Unlike DELETE, TRUNCATE cannot be filtered with a WHERE clause and in most engines cannot be rolled back once committed. Auto-increment counters are also reset by TRUNCATE in MySQL and MariaDB.

Remove All Rows From A Table With TRUNCATE

Remove all rows from a staging table, then verify it is empty

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.