SQL TRUNCATE TABLE

SQL TRUNCATE TABLE Statement

The SQL TRUNCATE TABLE statement is used to remove all rows from a table, effectively resetting the table to its empty state while preserving its structure. This operation is faster than using the DELETE statement without a WHERE clause because it does not generate individual row delete actions.

Syntax

TRUNCATE TABLE table_name;
  • TRUNCATE TABLE: This is the SQL keyword used to delete all rows from a table.
  • table_name: This specifies the name of the table you want to truncate.

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then truncating the table.

Step 1: Creating a Database

This step involves creating a new database where the table will be stored.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');

Here, we insert two rows of data into the employees table.

Step 4: Truncating the Table

This step involves removing all rows from the employees table, effectively resetting it.

TRUNCATE TABLE employees;

This command will delete all rows from the employees table, leaving it empty but still retaining its structure.

To verify that the table has been truncated, you can use the following command to select all rows:

SELECT * FROM employees;

This command will return an empty result set, confirming that all rows have been removed.