Sometimes you may need to empty table content in SQL server, or delete all rows in a table. It is also known as table truncation. In this article, we will look at how to truncate table in SQL Server.
How To Truncate Table in SQL Server
Here are the steps to truncate table in SQL Server using TRUNCATE TABLE command. Please be very careful when you use this command, as there is no way to undo its result.
Here is the syntax of TRUNCATE TABLE command
TRUNCATE TABLE table_name
You just need to specify table name that you want to truncate.
Also read : How to Rename Table in SQL Server
Let us say you have a table sales(id, sale_date, amount)
# create table sales(id int, sale_date date, amount int); # insert into sales(id, sale_date, amount) values(1,'2021-01-01',100), (2,'2021-01-02',200); # select * from sales; +------+------------+--------+ | id | sale_date | amount | +------+------------+--------+ | 1 | 2021-01-01 | 100 | | 2 | 2021-01-02 | 200 | +------+------------+--------+
Also read : How to Update Multiple Columns in SQL Server
Here is the command to truncate sales table in SQL Server.
# truncate table sales; # select * from sales; Empty set (0.00 sec)
As you can see, the table content has been emptied.
Please note, if your table has auto increment column, its value will be reset after TRUNCATE TABLE statement.