SQL Truncate Table

Filed Under: SQL

SQL Truncate Table

Assume a case when we want to have the database but we would like to delete the data of the table. In such cases, TRUNCATE command is used to delete just the data from the table. The table structure will remain as it is.

Syntax for SQL TRUNCATE

TRUNCATE TABLE <table_name>;

We will try to understand how the TRUNCATE command works in the following databases.

  1. MySQL
  2. PostgreSQL
  3. SQL Server

MySQL Truncate Table

Let us first create a Teacher table and we will insert data in the table as follows.

Create Table: –

CREATE TABLE `teacher` ( `TeacherId` INT NOT NULL, `TeacherName` VARCHAR(45) NULL, `State` VARCHAR(45) NULL, `Country` VARCHAR(45) NULL, PRIMARY KEY (`TeacherId`), UNIQUE INDEX `TeacherId_UNIQUE` (`TeacherId` ASC) VISIBLE);

Insert Data: –

Insert into Teacher(TeacherId,TeacherName,State,Country) VALUES (1, 'Amit','Bengaluru','India'), (2, 'Harry','Texas','US'), (3, 'John','London','UK'), (4, 'Rohit','Delhi','India');

Let us validate if the table creation and data insertion worked fine using the following query.

Select * from teacher;
SQL Insert Into Select

Table After SQL Insert

Now we will TRUNCATE the data of the table using the following command.

TRUNCATE TABLE teacher;
MySQL Truncate Table

MySQL Truncate Table

We will recheck if the data still exists in the table using the following query.

Select * from Teacher;
MySQL Table After Truncate

MySQL Table After Truncate

 

PostgreSQL Truncate Table

We will create the same table in PostgreSQL table

Create Table: –

CREATE TABLE public."Teacher"
(
"TeacherId" bigint,
"TeacherName" character varying,
"State" character varying,
"Country" character varying,
PRIMARY KEY ("TeacherId")
)
Insert Data: -
INSERT INTO public."Teacher"(
	"TeacherId", "TeacherName", "State", "Country")
	VALUES  (1, 'Amit','Bengaluru','India'), (2, 'Harry','Texas','US'), (3, 'John','London','UK'), (4, 'Rohit','Delhi','India');

Let us validate if the table creation and data insertion worked fine using the following query.

Select * from teacher;

PostgreSQL Table After Insert

PostgreSQL Table After Insert

Now we will TRUNCATE the data of the table using the following command.

TRUNCATE TABLE teacher;
PostgreSQL Truncate Table

PostgreSQL Truncate Table

We will recheck if the data still exists in the table using the following query.

Select * from Teacher;

PostgreSQL Table After Truncate

PostgreSQL Table After Truncate

SQL Server Truncate Table

We have already created the table and have inserted the data using following commands.

Create Table: –

CREATE TABLE teacher ( TeacherId INT NOT NULL, TeacherName VARCHAR(45) NULL, State VARCHAR(45) NULL, Country VARCHAR(45) NULL, PRIMARY KEY (TeacherId));

Insert Data: –

Insert into Teacher(TeacherId,TeacherName,State,Country) VALUES (1, 'Amit','Bengaluru','India'), (2, 'Harry','Texas','US'), (3, 'John','London','UK'), (4, 'Rohit','Delhi','India');

Let us validate if the table creation and data insertion worked fine using the following query.

Select * from teacher;

SQL Server Table After Insert

SQL Server Table After Insert

Now we will TRUNCATE the data of the table using the following command.

TRUNCATE TABLE teacher;
SQL Server Truncate Table

SQL Server Truncate Table

We will recheck the data in the table using the following query.

Select * from Teacher;
SQL Server Table After Truncate

SQL Server Table After Truncate

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages