SQL Tutorial SQL Advanced SQL Database SQL Resources
Python Java C++ C C# PHP R SQL DS Algo InterviewQ

SQL - TRUNCATE TABLE



The SQL TRUNCATE TABLE statement is used to delete complete data from an existing table. The SQL DROP TABLE statement can also be used to delete complete data of a table but it will delete whole table structure from the database. Hence, SQL TRUNCATE TABLE statement is useful when a table need to be emptied but the table structure is retained.

Note: Be careful before truncating a table. Once deleted, all data stored in that table will be lost forever!.

Syntax

The syntax of using TRUNCATE TABLE statement is given below:

TRUNCATE TABLE table_name;

Example:

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

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

The description of the table can be checked using DESC command as shown below:

DESC Employee;

FieldTypeNullKeyDefaultExtra
EmpIDint(11)NoPRI
Namevarchar(255)No
Cityvarchar(100)YesNULL
Ageint(11)YesNULL
Salarydecimal(18,2)YesNULL

To truncate this table, the SQL code is shown below:

TRUNCATE TABLE Employee;

After truncating the table, the DESC command will still show the same structure as shown above but the table contains no records.

SELECT * from Employee;

This result of the above code will be:

EmpIDNameCityAgeSalary

5