Generate test data in SQL Server

Source: Test data generated in SQL Server

Brief introduction

In the actual development process. In many cases we need to insert a large amount of test data into the database to test the functionality of the program. The resulting test data often needs to conform to specific rules. Although it is possible to write a program yourself to insert data, it is not advisable to write a program that inserts data into each project. This article focuses on generating test data in SQL Server using the VS2010 data generation plan.

Ways to generate test data

1. Manual editing

In the development process, very small amounts of data can be inserted manually. The disadvantage of this method can be imagined ... inserting 100 data is enough for you to busy one morning.

2. Write the program &t-sql statement to insert

This shortcoming is also obvious, the development efficiency is also under. The program needs to be modified or rewritten for different programs to be developed again. Even writing a piece of code for each table, and the resulting data flexibility is not high!

For example, to generate 1000 data for a table I might need to write so many T-sql:

As you can see, this approach is not only cumbersome, but the resulting test data may not match what we need.

3. Using data from a system that is already online

Well, it looks good. Simple and easy, the amount of data is sufficient. But leaving the new system or a completely different system table structure change may not be able to use the data already on the go. Take the customer's business data for testing. This is too moral integrity ....

Generate test data using the VS2010 data generation plan

The data generation plan provided by VS2010 is a powerful tool. It can efficiently generate test data, with built-in data generation rules that make it easy for us to generate the data we need. Let's look at a practical example:

For the sake of simplicity, the generated data is structured with only two tables (Employee table and departmental table), which are connected by a foreign key:

In VS2010 Create the database project, add the SQL Server 2008 database project, and then add the data generation plan:

Create a database connection in VS2010, add a new item, and in the data generation plan, you can see the two tables:

By specifying the properties of a column, I can adjust the specification of the data I generate:

Below, I specify for several column data for the employee table, the Name column, I specify a minimum length of 4, and a maximum length of 6. The gender column is only allowed to have two values, male and female. e-mails follow regular expressions to generate values that conform to the email address specification:

Sex column designation only male and female

Message column specifies the regular expression of the message

In the data generation plan, VS2010 provides the power to generate data with foreign key constraints. In the above two tables, assuming that the company has 1000 employees, there are 10 parts, corresponding to each generated department data will generate 100 employee data, I can be in the "Related tables" and "Related table settings":

When everything is ready, I can generate data by pressing F5:

To view data in SSMs:

As you can see, the data basically matches the data I need to generate.

Generate test data in SQL Server