SQL TRUNCATE TABLE Example

Let’s understand TRUNCATE in SQL with examples. Here we will look at different examples of the SQL TRUNCATE TABLE command.

First, we will create a demo SQL database and table, on which we will use the TRUNCATE TABLE command.

SQL

CREATE DATABASE w3wiki;
USE w3wiki;
CREATE TABLE EMPLOYEE(
    EMP_ID INT(4),
    NAME VARCHAR(20),
    AGE INT(3),
    DOB DATE,
    SALARY DECIMAL(7,2));
INSERT INTO EMPLOYEE VALUES(121,'AJAY KUMAR',23,'1987-09-12',23456.32);
INSERT INTO EMPLOYEE VALUES(132,'BOBBY DEOL',34,'1989-02-19',84164.56);
INSERT INTO EMPLOYEE VALUES(246,'ELVISH SMITH',27,'1996-01-29',51876.97);
INSERT INTO EMPLOYEE VALUES(955,'GEORGE CLARKE',31,'1992-09-21',91451.64);
INSERT INTO EMPLOYEE VALUES(729,'MONICA GELLER',28,'1985-11-18',98329.43);

The following table will be created.

Records in the table

TRUNCATE TABLE in SQL Example

In this example, we will Truncate the created table.

Query:

TRUNCATE TABLE EMPLOYEE;

Truncating data

After truncating data of our table, the data of our table has been erased but the structure is preserved so now if we perform SELECT * FROM EMPLOYEE command on our table we will see everything is erased and an empty set is being returned.

No data is returned

But let’s now check whether the structure of the table is deleted or it has been preserved so we again use the DESC command to see the structure of the table and we will see that the structure remains as it is.

Structure is preserved