SQL: TRUNCATE TABLE Statement
This SQL tutorial explains how to use the SQL TRUNCATE TABLE statement with syntax and examples.
Description
The SQL TRUNCATE TABLE statement is used to remove all records from a table. It performs the same function as a DELETE statement without a WHERE clause.
Syntax
The syntax for the TRUNCATE TABLE statement in SQL is:
TRUNCATE TABLE table_name;
Parameters or Arguments
- table_name
- The table that you wish to truncate.
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!
Example
You might choose to truncate a table instead of dropping the table and recreating it. Truncating a table can be faster and does not affect any of the table's indexes, triggers, and dependencies. It is also a fast way to clear out the records from a table if you don't need to worry about rolling back.
Let's look at an example of how to use the TRUNCATE TABLE statement in SQL.
In this example, we have a table called suppliers with the following data:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Microsoft | Redmond | Washington |
| 200 | Mountain View | California | |
| 300 | Oracle | Redwood City | California |
| 400 | Kimberly-Clark | Irving | Texas |
| 500 | Tyson Foods | Springdale | Arkansas |
| 600 | SC Johnson | Racine | Wisconsin |
| 700 | Dole Food Company | Westlake Village | California |
| 800 | Flowers Foods | Thomasville | Georgia |
| 900 | Electronic Arts | Redwood City | California |
Enter the following TRUNCATE TABLE statement:
Try ItTRUNCATE TABLE suppliers;
Then select the data from the suppliers table again:
SELECT * FROM suppliers;
These are the results that you should see:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
This example would truncate the table called suppliers and remove all records from that table. It would be equivalent to the following DELETE statement in SQL:
Try ItDELETE FROM suppliers;
Both of these statements would result in all data from the suppliers table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you may not be able to roll back the TRUNCATE TABLE statement in all SQL databases.
If you need to prefix the table name with the database name, you can rewrite the TRUNCATE TABLE statement as follows:
TRUNCATE TABLE totn.suppliers;
This example would truncate the table called suppliers in the database called totn.
Advertisements