Summary -

In this topic, we described about the below sections -

Why to truncate a table?

Table consists of data in the form of rows and columns. The data is inserted to the table in various ways. The reasons behind of deleting the table data are –

  • Data might insert wrongly
  • Data might have loaded incorrectly.
  • The data storage requirement has fulfilled (like pending bills, pending transactions, pending payments etc,.) and many more.

DELETE FROM statement fulfils the requirement of deleting the data. Apart from this DELETE FROM statement, TRUNCATE TABLE statement also used to delete the data from the table.

Note :- TRUNCATE statement does exactly same as DELETE FROM statement does but without a WHERE clause.

How to use TRUNCATE TABLE?

TRUNCATE TABLE is used to delete all the information in existing table, but structure of the table will remain same. By using TRUNCATE TABLE Statement, we cannot delete specified rows of data in table and WHERE clause does not exists in TRUNCATE TABLE Statement.

Syntax -

TRUNCATE TABLE table_name;
  • table_name – Represents name of the table which is used to truncate.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

In the below example, we are trying to delete rows from employee_details table. The query for the same is -

TRUNCATE TABLE employee_details;

If the above query executed successfully, the data from employee_details table gets deleted. However, the employee_details table existing in the database.

Warning :- Be cautious while triggering TRUNCATE TABLE query. Because the data deleted by using TRUNCATE TABLE statement can't revert back. So, think twice while applying the TRUNCATE TABLE statement.

Verifying table

Once the TRUNCATE TABLE statement executed successfully, then we can conclude that the table data deleted successfully. However, it is always best practice to verify once before confirming to the external world.

To verify the table data gets deleted or not, just trigger a select query on the table like below -

SELECT  * FROM  employee_details;

If it displays empty table like below, then it confirms that the table data got deleted successfully.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id