TRUNCATE TABLE statement empties a table completely. Logically, it is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements.
Following is the syntax of TRUNCATE TABLE statement:
TRUNCATE [TABLE] table_name;
It will delete all data in a table
table_name. The TABLE keyword is optional. If there is any FOREIGN KEY constraints from other tables which reference the truncate table, above statement will fail.
Following example shows usage of TRUNCATE statement on table
# Create Table CREATE TABLE employleinfo(id int, firstName varchar(50), lastName varchar(50), age int, city varchar(50)); # Insert record into the table INSERT into employleinfo values(1, "Rahul", "Bhagwat",20,"Noida"); INSERT into employleinfo values(2, "Stephen", "Fleming", 17, "Delhi"); INSERT into employleinfo values(3, "Sebastian", "Smith", 19, "Patna"); INSERT into employleinfo values(4, "Anjali", "Sharma", 50, "Mumbai"); INSERT into employleinfo values(5, "Erica", "Edwards", 40, "Chennai"); # Empty Table TRUNCATE TABLE employleinfo; # Select from Table SELECT * FROM employleinfo;
DELETE vs TRUNCATE
- Unlike a DELETE statement, the number of rows affected by the TRUNCATE TABLE statement is 0.
- TRUNCATE TABLE is more efficient than the DELETE statement. Because TRUNCATE drops and recreates the table instead of deleting rows one by one.
- DELETE statement can delete records that have foreign key constraints defined. A TRUNCATE cannot be executed if these same constraints are in place.