Oct 30, 2020 by Robert Gravelle

The periodic reverting of database instances to a baseline dataset is a common practice in development and test environments. Case in point, the office where I work does so on a regular basis, whenever data diverges too much from the baseline. This is required because developers and automated tests expect the data to be of a certain quantity and quality. There is no right way to overwrite table contents, so you should choose an option based on your organization's particular goals and circumstances. In today's blog, I'll share what we do where I work as well as my standard process at This email address is being protected from spambots. You need JavaScript enabled to view it..

SQL Scripting at Work

An SQL script is one that contains a set of SQL commands saved as a file, typically with a .sql extension. An SQL script can contain both SQL statements or PL/SQL blocks. SQL scripts provide a simple means of grouping related SQL functionality for the purpose of reusing whenever needed. All popular relational databases can run an SQL script directly from the command line. For example, in MySQL, you can invoke the SQL script as follows:

shell> mysql --user="username" --database="databasename" --password="yourpassword" < "path to sql file"

Creating a Table Refresh Script

In my experience, the easiest way to create a script to reset table data is to use a dump utility. Keeping with MySQL, the installation process includes the mysqldump utility. It can create SQL statements to both truncate the table and repopulate rows with baseline data. Mysqldump has a number of options, but all that is really needed are the database and SQL file names:

shell> mysqldump db_name > backup-file.sql 

Which ever utility you use, it's crucial that the generated SQL includes a DROP TABLE statement before the table population. Mysqldump has a --add-drop-table option, but it's ON by default, so you don't need to include it under normal usage.

Navicat database development and admin clients include a Dump SQL File command. Like mysqldump, it also provides many options, including whether to dump both the structure and data or structure only:

Here is a sample generated file in the Navicat SQL Editor. As you can see, the DROP TABLE IF EXISTS command precedes the CREATE statement:

Truncating a Table

Whereas the above script recreates the table from scratch, you can also TRUNCATE a table and then re-insert the data from a backup table using the INSERT INTO command:

TRUNCATE TABLE dbo.T1; 
INSERT INTO D1.dbo.T1 
SELECT * FROM D2.dbo.T1;

Here's an example in Navicat:

Note that, behind the scenes, the database is still dropping the table and re-creating it via the SQL CREATE TABLE statement. Besides being faster than the EMPTY TABLE command, TRUNCATE TABLE resets all auto-increment fields to start over at 1, which is usually preferable letting them run.

Conclusion

In today's blog, we explored a couple of ways to reset table data to a baseline for development and test environments. Navicat can help create .sql scripts as well as execute them with ease. Moreover, its Automation Tool can schedule scripts to run according to a variety of schedules so that you can set up your jobs and then let Navicat handle the rest.