SQL TRUNCATE TABLE command

SQL TRUNCATE TABLE command

The SQL TRUNCATE TABLE command is used to delete all data from an existing data table.

You can also use the DROP TABLE command to delete the entire data table, but the DROP TABLE command not only deletes all the data in the table, but also removes the entire table structure from the database. If you want to re-store data in a table, you must rebuild the data table.

Grammar:

The basic syntax of TRUNCATE TABLE is as follows:

TRUNCATE TABLE  table_name;

Example:

Consider the CUSTOMERS table, which shows the following:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following example shows how the TRUNCATE command is used:

TRUNCATE TABLE CUSTOMERS;

Now that the CUSTOMERS table has been emptied, the output of the SELECT statement should look like this:

SQL> SELECT * FROM CUSTOMERS;
Empty set (0.00 sec)