POST_START

Removing All Rows While Preserving a Table with TRUNCATE

I recently had the task of cleaning up some data in the training.sales.customer_master table. The goal was to remove all rows from the table while keeping the table structure intact. This is a common operation when you need to reset a table for testing or reprocessing, and I learned that the right tool for the job is the TRUNCATE command.

Understanding the Current State of the Table

Before making any changes, I wanted to understand the current state of the table. I ran a SELECT COUNT(*) to see how many rows were present. This gave me a baseline to compare against after performing the TRUNCATE operation.

SELECT COUNT(*) FROM training.sales.customer_master;

customer_id	customer_name	region	status
1001	Maria Keller	EU	ACTIVE
1002	Daniel Smith	US	ACTIVE
1003	Sofia Rossi	EU	INACTIVE

I noticed that there were three rows in the table. This confirmed that the table had data and that a cleanup was necessary.

Truncating the Table to Remove All Rows

With the baseline established, I decided to proceed with the TRUNCATE command. This operation would remove all rows from the table while preserving its structure, including constraints, indexes, and triggers. I ran the following command:

TRUNCATE TABLE training.sales.customer_master;

The response from the system indicated that the operation was successful. I had effectively removed all rows from the table without altering its schema. This was exactly what I needed for the next phase of my work.

Verifying the Result of the Truncate Operation

To ensure that the TRUNCATE operation had worked as expected, I ran the SELECT COUNT(*) command again. This time, I expected the result to be zero since all rows had been removed.

SELECT COUNT(*) FROM training.sales.customer_master;

customer_id	customer_name	region	status
0

The result confirmed that the table was now empty. I had successfully removed all rows while preserving the table structure. This was a critical step in my workflow, and I was glad to have mastered the TRUNCATE command for such scenarios.