BBampaa

Lessons › SQL › Deep Dive: Database Admin & ETL

SQL lesson

TRUNCATE TABLE

TRUNCATE TABLE wipes every row out of a table while leaving the table's structure in place and ready to accept new data.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Clearing a Retail Staging Table with TRUNCATE TABLE

TRUNCATE TABLE returns_staging

Result:

Step by step

  1. TRUNCATE TABLE removes every row but keeps the table itself. Faster than DELETE for clearing a big table.
    TRUNCATE TABLE returns_staging;
  2. RESTART IDENTITY also resets any auto-increment columns back to 1.
    TRUNCATE TABLE returns_staging RESTART IDENTITY;
  3. CASCADE truncates dependent tables too, in one go.
    TRUNCATE TABLE returns_staging RESTART IDENTITY CASCADE;

Practice

Try each one in your head first, then open the answer.

1. Write a statement to remove all rows from products but keep the table structure.
TRUNCATE TABLE products
2. Write a statement to remove all rows from customers but keep the table structure.
TRUNCATE TABLE customers
3. Write a statement to remove all rows from orders but keep the table structure.
TRUNCATE TABLE orders
4. Write a statement to remove all rows from inventory but keep the table structure.
TRUNCATE TABLE inventory
5. Write a statement to remove all rows from suppliers but keep the table structure.
TRUNCATE TABLE suppliers

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed