Test data generated in SQL Server

Introduction

In the actual development process. In many cases, we need to insert a large amount of test data into the databaseProgram. The generated test data often needs to comply with specific rules. Although you can write a program to insert data, it is unwise to write a program to insert data for each project. This article describes how to use vs2010 data generation plan to generate test data in SQL Server.

 

How to Generate Test Data

1. manually edit

During development, a very small amount of data can be inserted manually. The disadvantages of this method can be imagined .... You can just insert 100 data records in the morning.

 

2. Write Program & T-SQL statement to insert

This disadvantage is also obvious, with the same development efficiency. When developing different programs again, the program needs to be modified or rewritten. Even for each table, you need to writeCodeAnd the generated data is not flexible!

For example, to generate 1000 pieces of data for a table, I may need to write so many T-SQL:

It can be seen that this method is not only troublesome, but the generated test data may not meet our needs.

 

3. Use the data of the online system

Well, this method looks good. It's simple and easy, with enough data. However, aside from the fact that new systems or completely different system table structures may not be able to use online data. Take the customer's business data for testing. This is too cool ......

 

Use vs2010 data generation plan to generate Test Data

The data generation plan provided by vs2010 is a powerful tool. It can generate test data efficiently. The built-in data generation rules can easily generate the required data. The following is an example:

For the sake of simplicity, the generated data architecture only has two tables (employee table and department table), and is connected with a foreign key:

Create a database project in vs2010, add an SQL Server 2008 database project, and then add a data generation plan:

Create a database connection in vs2010 and add a new project. In the data generation plan, you can see the two tables:

By specifying the attribute of a column, I can adjust the specifications of the data generated by me:

Below, I will specify the data of several columns in the employee table. For the name column, I will specify a minimum length of 4 and a maximum length of 6. the Gender column can have only two values: male and female. in contrast, emails generate values that comply with the email address specifications according to Regular Expressions:

Only male and female are specified in the Gender column.

Regular Expression of the specified email in the email Column

In the data generation plan, vs2010 provides powerful functions and foreign key constraints to generate data. In the preceding two tables, assume that the company has 1000 employees and 10 employees. Each department generates 100 employees, I can do this in "related tables" and "related table settings ":

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

View data in SSMs:

We can see that the data basically meets the data we need to generate