intro
Let’s learn everything you need to know about the truncate many tables SQL approach to emptying multiple tables at the same time.
If you ever worked with a database, you are probably familiar with the TRUNCATE operation to empty a table. Since data decluttering is often ongoing, you might want to clean multiple tables at once. So, is there a truncate many tables SQL operation? Which databases support it, and how can you execute it?
In this guide, you will find answers to these questions and learn how to truncate multiple tables in SQL. Let’s dive in!
What is Truncating?
Truncating refers to the process of removing all data in a given table at once. It’s different from a DELETE statement in the following ways:
A TRUNCATE query will always be faster than a DELETE query because TRUNCATE doesn’t come with the same overhead as a DELETE — if you need to delete all rows within a table at once, ditch DELETE and use TRUNCATE instead.
Why Truncate Multiple Tables in SQL Simultaneously?
Truncating multiple tables in SQL simultaneously can come in handy in many scenarios and for a variety of reasons, including:
Time to discover how to perform this useful operation in SQL!
Truncating Multiple Tables in SQL: A Step-by-Step Guide
Even though the TRUNCATE statement is part of the ANSI/ISO SQL standard, popular databases provide different implementations. MySQL, SQL Server, and Oracle have a similar syntax for it, while PostgreSQL extends it with special features.
In this section, you will see different truncate many tables SQL approaches—including using a database client like DbVisualizer.
MySQL, SQL Server, and Oracle
MySQL, SQL Server, and Oracle do not support an explicit syntax to truncate multiple tables in a single query. Instead, you need to run multiple TRUNCATE statements individually, as shown below:
1
TRUNCATE TABLE table_name_1;
2
-- ...
3
TRUNCATE TABLE table_name_n;
You may be tempted to wrap the above query in a single transaction, but keep in mind that TRUNCATE statements cause an implicit commit in MySQL and Oracle. So, that would not make sense or even be allowed. Only SQL Server supports rolling back TRUNCATE TABLE operations.
PostgreSQL
In PostgreSQL, the TRUNCATE TABLE operation supports truncating multiple tables with a single query with the following syntax:
1
TRUNCATE TABLE table_name_1, table_name_2, ..., table_name_n;
This allows you to efficiently perform a truncate many tables SQL operation.
With a Database Client
Truncating tables via SQL queries is a powerful operation, but what if you prefer a visual approach? With a full-featured, multi-database visual client like DbVisualizer, you can perform this operation effortlessly!
To get started, download DbVisualizer for free, install it, and set up a connection to your database. Then, in the left-hand column, navigate to your tables:
Select the tables you want to truncate by holding Ctrl (or Command on macOS), right-click, and select the "Empty Table…" option:
Next, a modal window will appear where you can customize the truncation behavior:
When ready, click "Execute" to truncate the tables, or copy the generated SQL query to execute it manually.
Well done! Truncating multiple tables has never been easier with DbVisualizer.
Note: This feature is available only in the Pro version of DbVisualizer. Take advantage of the 21-day free trial to explore all the features it has to offer!
Truncate Many Tables SQL Best Practices
Below is a list of best practices to truncate many tables in SQL like a pro:
Conclusion
In this guide, you saw why you may need to truncate multiple tables at once in SQL and how to do it in MySQL, SQL Server, and PostgreSQL. You also discovered that only PostgreSQL truly supports truncating multiple tables in a single query.
As shown here, truncating multiple tables becomes much easier with a visual database client like DbVisualizer. This tool greatly simplifies query execution, data exploration, and table discovery, while also offering advanced features like query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
What is the truncate many tables SQL query syntax?
PostgreSQL is the only major database that supports a special syntax for truncating multiple tables at once. Find the syntax below:
1
TRUNCATE TABLE table_name_1, table_name_2, ..., table_name_n;
This allows truncating multiple tables in a single operation.
Is it possible to truncate multiple tables at once in SQL?
Yes, some databases allow you to truncate multiple tables at once in SQ. In PostgreSQL, you can use the TRUNCATE TABLE statement with a comma-separated list of table names to truncate multiple tables in a single query. However, most other major databases, including MySQL, SQL Server, and Oracle, do not support this feature and require individual TRUNCATE statements for each table.
How can you truncate all tables in a database?
To truncate all tables in a database, you need to truncate each table individually. What you need to do is loop through the tables and execute TRUNCATE for each one. You can find the names of the tables in a database in the system tables.
Why should you use a database client to truncate multiple tables in SQL?
Using a database client to truncate multiple tables in SQL offers several advantages:
What are some considerations for truncating multiple tables in SQL?
When performing a truncate many tables SQL query, consider the following:
