How to generate Meaningful Test Data in SQL Server

Published by JordyDev on 20 November, 2015
Without meaningful test data in the test environment, that represents of what you will end up with in the production, it is quite impossible to predict the way the application will behave after the release.

Step 1. Select the right tool
To generate meaningful test data in SQL Server you need Data Generator for SQL Server tool tool that includes 180+ various generators that you may find useful in many cases.

Step 2. Start generating.
After installing the tool complete the next steps:
1. On the Standard toolbar, click New Data Generation.
2. Specify the connection and select a required database.
3. The Options page will appear. On the Options page, set required options and click Open.

The data generation document window opens. Now you can select columns to be populated, select and customize a generator to use for each separate column. To populate all the columns, select the check box next to the salesdb. You can see the data to be generated (in real-time) in the Preview window.

Step 3. Selecting generators for individual columns

For a column you select, Data Generator automatically assigns a generator based on the column and table characteristics: name, data type, etc. You may select a different generator for the column and customize the parameters of this generator in the settings panel.

The CustomerID column

Select the CustomerID column. The ID (sequential) generator is automatically assigned, which generates unique identifiers for the column. For this generator, along with the basic settings, you can specify the Min and Max values for the IDs to be generated.

The FirstName column

The First Name (any gender) generator is automatically assigned to this column. The generator provides realistic male first names. In the text box, you can see a predefined regexp template to generate names. The template may be customized by inserting various snippets, counters, files, and columns.

The MiddleInitial column

Select the MiddleInitial column. The Middle Name generator is automatically assigned to this column. The generator provide a wide range of middle names including both the full names and initials. The values to be generated may be customized using regular expressions. For example, if you want only one-character initials to be generated, specify the [A-Z] regular expression in the text box of the settings pane. The values may also be adjusted by inserting predefined regexp snippets, counters, files, and columns.

The Gender column

Select the Gender column. The Gender generator is automatically assigned to this column. The data grid in the settings pane is designed to show weight ratios and percentages of the male and female values to be generated. The weight ratios can be customized by manually altering the numbers in the data grid. The percentages will be automatically recalculated concurrently with the change of the weight ratios.

The SSN column

Select the SSN column. The US Social Security Number (SSN) is automatically assigned to this column. The generator will populate the column with random numbers. You can customize the numbers in the text box using various regular expressions or by inserting regexp snippets, counters, files, and columns.

For example, with the help of regexp snippets, you can add or generate lower case and uppercase letters, hexadecimal characters, digits without a zero. You can also specify groups, alternatives, repeats, etc. of the generated values.

With the help of the counter, you may can get a sequence of digits in increment/decrement order, sequence of characters in increment/decrement order, and hexadecimal characters in increment/decrement order.

The Avatar column

Select the Avatar column. The Avatar Images generator is automatically assigned to this column. The generator contains more than 150 different avatars. You can specify the fold and a subfolder to load images from, as well as select a file pattern.

The ShippingMethod column

Select the ShippingMethod column. The Shipping Method generator is automatically assigned to this column. The generator provides a wide range of realworld shipping methods, such as USPS Express Mail, FedEx, DHL, Airmail Priority, etc. The list of all shipping methods is visible in the text box of the settings pane. You can customize this list manually or by adding regexp snippets, counters, columns, files from the drop-down lists below the text box.

While selecting tables and customizing generators, the Preview window displays data to be generated in real-time.

Well, we generated all required data for our table. Now, it is time to populate the database with this data!

Step 4. Populating Database

To populate the database, on the Data Generator toolbar, click Populate data to the target database…

Then you can open the SQL script in the internal editor to customize it, save this script to a file, or execute it to populate the target database.

On the next step, you can configure the synchronization options.

The Summary tab allows you to see the data population actions and warnings.

The last thing you need to do is to click Generate to finish the process.

As you can see, the overall process is quite intuitive and doesn’t require too much time to generate large volume of meaningful test data!

You can perform the same method if you have MySQL database. You need Data Generator for MySQL tool for this purpose.