Walkthrough – Generate Test Data With Red Gate SQL Data Generator

In this walkthrough I will show you how to use Red Gate SQL Data Generator to generate test data for a database. While writing software which interacts with a database developers often write scripts to insert some data. Usually this data is not big enough to find performance bottlenecks and/or issues with presenting data on UI. An application may work well under the load of dev data and possibly test data but becomes a different beast when run against proper production data. So the question is how do you get some real looking data. There are two options; one you can ask your friendly DBA to give it to you, second you can generate your own data which mimics production data at-least in size. In this post I will deal with the second. I will show you how easy it is to generate good quality test data.

As a disclaimer I’d like to say that Red Gate did not pay for this post in any form. I installed a 14 day trial of Red Gate SQL Data Generator and I liked the tool.

Creating SQL Data Generator Project

SQL Data Generator works with projects which are .sqlgen files. The project file stores settings such as database you are generating data for and few other settings such as should Insert Triggers be fired. A project can be created by clicking on the New Project  button on the toolbar which brings this dialog

You can specify the database server and the database name  here. For this walkthrough I will generate data for Northwind database. After supplying database values click OK and it will import schema information from database.

The main windows displays the tables for which data will be generated. You can exclude tables by clicking on the checkbox.

On the right side of the window you can set the number of rows to be generated, what should be done when data is invalid and you can choose if existing data should be deleted.

For this example let’s set number of rows to be 100,000 for customer table and do the same for few other tables. I think that is a good number to do some testing.

Clicking Generate Data button on the toolbar presents this dialog which presents a summary of actions which will be taken.

Once the data is generated successfully a report is presented which shows the number of rows inserted for tables.

A quick query on Northwind database in SSMS shows a sample of newly inserted data.

And that’s how easy it is to generate data which can be used for load testing, and finding performance bottlenecks while developing an application.