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
|
1 |
TRUNCATE TABLE <table_name>; |
We will try to understand how the TRUNCATE command works in the following databases.
- MySQL
- PostgreSQL
- SQL Server
MySQL Truncate Table
Let us first create a Teacher table and we will insert data in the table as follows.
Create Table: –
|
1 |
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: –
|
1 |
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.
|
1 |
Select * from teacher; |
Table After SQL Insert
Now we will TRUNCATE the data of the table using the following command.
|
1 |
TRUNCATE TABLE teacher; |
MySQL Truncate Table
We will recheck if the data still exists in the table using the following query.
|
1 |
Select * from Teacher; |
MySQL Table After Truncate
PostgreSQL Truncate Table
We will create the same table in PostgreSQL table
Create Table: –
|
1 2 3 4 5 6 7 8 |
CREATE TABLE public."Teacher" ( "TeacherId" bigint, "TeacherName" character varying, "State" character varying, "Country" character varying, PRIMARY KEY ("TeacherId") ) |
|
1 |
<strong>Insert Data: -</strong> |
|
1 2 3 |
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.
|
1 2 |
Select * from teacher; |
PostgreSQL Table After Insert
Now we will TRUNCATE the data of the table using the following command.
|
1 |
TRUNCATE TABLE teacher; |
PostgreSQL Truncate Table
We will recheck if the data still exists in the table using the following query.
|
1 2 |
Select * from Teacher; |
PostgreSQL Table After Truncate
SQL Server Truncate Table
We have already created the table and have inserted the data using following commands.
Create Table: –
|
1 |
CREATE TABLE teacher ( TeacherId INT NOT NULL, TeacherName VARCHAR(45) NULL, State VARCHAR(45) NULL, Country VARCHAR(45) NULL, PRIMARY KEY (TeacherId)); |
Insert Data: –
|
1 |
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.
|
1 2 |
Select * from teacher; |
SQL Server Table After Insert
Now we will TRUNCATE the data of the table using the following command.
|
1 |
TRUNCATE TABLE teacher; |
SQL Server Truncate Table
We will recheck the data in the table using the following query.
|
1 |
Select * from Teacher; |
SQL Server Table After Truncate