Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to generate test data in SQL Server". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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 function of the program. The generated test data often need to comply with specific rules. Although you can write your own program to insert data, it is unwise to write a program for each project to insert data. This article mainly introduces the use of VS2010's data generation plan to generate test data in SQL Server.

The method of generating test data

1. Manual editing

During the development process, a very small amount of data can be inserted manually. The shortcomings of this method can be imagined. . Inserting 100 pieces of data will keep you busy all morning.

two。 Write programs & T-SQL statements to insert

This disadvantage is also obvious, and the development efficiency is also low. When you develop a different program again, the program needs to be modified or rewritten. Even a piece of code is written for each table, and the generated data is not very flexible!

For example, I might need to write so much T-SQL to generate 1000 pieces of data on a table:

As you can see, this method is not only troublesome, but also the generated test data may not meet our needs.

3. Use the data of the system that is already online

Well, this method seems to be good. It is simple and easy, and there is enough data. But put aside the new system or a completely different system table structure change may not be able to use the data already online. Test the customer's business data.. This is so unscrupulous.

Use VS2010's data generation plan to generate test data

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

For simplicity, the generated data is structured with only two tables (the employee table and the department table), joined by foreign keys:

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

Establish a database connection in VS2010, add new items, and you can see these two tables in the data generation plan:

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

Next, I specify several column data for the Employee table, the Name column. I specify that the minimum length is 4 and the maximum length is 6.Gender column, which allows only two values, male and female. Email generates a value that conforms to the Email address specification according to the regular expression:

Gender column specifies only men and women

The message column specifies the regular expression of the message

In the data generation plan, VS2010 provides powerful features such as foreign key constraints to generate data. In the above two tables, suppose the company has 1000 employees and 10 parts, and each department data generated is 100 employee data, which I can do in "related tables" and "related table settings":

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

View the data in SSMS:

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

This is the end of "how to generate Test data in SQL Server". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.