Basic about TRUNCATE TABLE Statement – using and example

This SQL tutorial explains how to use the SQL TRUNCATE TABLE statement with syntax and examples.

Description

The SQL TRUNCATE TABLE declaration is used to dispose of all data from a table. It performs the identical characteristic as a DELETE assertion except a WHERE clause.

Warning: If you truncate a table, the TRUNCATE TABLE assertion can not be rolled lower back in some databases.

Syntax

The syntax for the TRUNCATE TABLE assertion 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 comply with along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your personal database!

Get DDL/DML

Example

You would possibly pick to truncate a desk rather of shedding the desk and recreating it. Truncating a table can be quicker and does now not affect any of the table’s indexes, triggers, and dependencies. It is also a quickly way to clear out the records from a table if you do not need to fear about rolling back.

Let’s look at an example of how to use the TRUNCATE TABLE declaration 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 Google 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 It

TRUNCATE TABLE suppliers;

Then select the data from the suppliers desk again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city state

This example would truncate the desk referred to as suppliers and take away all archives from that table. It would be equivalent to the following DELETE statement in SQL:

Try It

DELETE FROM suppliers;

Both of these statements would result in all records from the suppliers table being deleted. The main difference between the two is that you can roll back the DELETE assertion if you choose, but you may not be capable to roll returned the TRUNCATE TABLE statement in all SQL databases.

If you need to prefix the desk title with the database name, you can rewrite the TRUNCATE TABLE announcement as follows:

TRUNCATE TABLE totn.suppliers;

This instance would truncate the desk known as suppliers in the database known as totn.