SQL Queries to Truncate, Drop or Rename a Table

SQL Queries to Truncate, Drop or Rename a Table:

Truncate Command

The truncate command in SQL is used to delete all the data from an existing table. You can also delete all the data from a table by using the DROP command but the drop command will destroy the structure of the table and you will have to create a new table in the database.

The truncate command on the other hand does not destroy the table structure and delete all the data safely.

SYNTAX

The syntax of the truncate command is as follows:

Truncate table table-name;

EXAMPLE

Consider the following example to truncate a table:

Truncate table employee;

This query will delete all the data that is in the student table. The difference between a delete command and a truncate command is that the dele command is used to delete all the rows from an existing table whereas; the truncate statement deletes all records in the table without destroying the structure and the table is like a newly created table.

The primary key is also re initialized after deleting all the records from table when used the truncate command.

DROP Command

The drop command is used to delete the complete table from a database and also destroys the structure of the table. The drop commands removes the whole definition of the table that is all the indexes, constraints, triggers, etc. are deleted. The information stored in the table is also lost when the DROP command is used and it cannot be recovered.

SYNTAX

DROP table table-name;

EXAMPLE

Suppose you have that is named as EMPLOYEE as follows:

If you want to drop the table data the information in the table will be lost and the structure of the table will also be destroyed. You will use the drop in the following way to delete the data.

Drop table employee;

Now the whole table will be diminished. The drop statement can also be used on databases. The syntax for this will be:

Drop database database-name;

This will drop the database from the system.

Rename Query

The table can be given a new name in a database by using the rename command. The syntax for rename command is gives below:

SYNTAX

Rename table old-table to new-table;

EXAMPLE
rename table employee to employee-track;

This query will rename or give a new name to an already existing table EMPLOYEE. The new name for this table will be EMPLOYEE-TRACK.