Generating SQL Server Test Data with Visual Studio 2010

By:   |   Comments (7)   |   Related: > Testing


Problem

As a database developer or tester sometimes you need to have production like data in your environment for your development or testing, but you cannot have the production data because of security and privacy issues. So how you can generate test data or replicate similar data as in production for your development or test environment?

Solution

Visual Studio Database edition provides several features for database development and testing. One of them is generating random test data using Data Generation Plan. Data Generation Plan contains how you want your test data to be generated for your specific tables and columns. It uses several built in data generators which generate random data or generate data from other data sources as per your column data type. You can change properties of these data generators to define the range and format of the data being generated as well. If it does not suffice your need or you have different business rules which can't be satisfied by using the built-in data generators, you can even create your own custom data generators too.

Note: In this demonstration I am going to show how you can generate test data in Visual Studio 2010 Ultimate edition although you can do the same with Visual Studio 2005/2008 Database edition too.

Launch Microsoft Visual Studio 2010 IDE (Integrated Development Studio) and open your database project. Right click on your project node in the Solution Explorer, go to Add and then click on New Item as shown below:

using microsoft visual studio 2010 ide

In Add New Item dialog box, select Data Generation Plan node in the left side under Database Project node and choose empty Data Generation Plan template from the detail section. Specify an appropriate name for your data generation plan as shown below and click on the Add button.

in the add new item dialog box select data generation plan

The Data Generation Plan will scan through the schema objects of your database project and will list all the tables that exist. By default all the tables are included in the plan for data generation. You can choose which tables you want to generate test data and how many records you want to generate for each table. Depending on the relationship defined the data generation plan shows related tables and you can specify a ratio for related tables for data generation as shown below.

The Data Generation Plan will scan through the schema objects of your database project and will list all the tables that exist

To start generating data, click on the Generate Data icon in the toolbar or hit F5. This will launch the Connect to Database wizard, as shown below, where you need to specify the database details where you want to generate the test data.

launch the connect to database wizard

specify databse connection

The moment you specify database connection and click on the OK button, the data generation plan starts generating data for each table selected in the plan and shows the status as follows. The wizard will also ask if you want to delete data from the target tables before data generation begins inserting rows.

the data generation plan starts generating data for each table selected in the plan and shows the status

To verify if the data has been generated, connect to your target database and query the record counts for those tables as shown below.

query the record counts for those tables

You don't need to generate test data to verify it in your target tables, but rather you can preview the data to be generated beforehand and generate test data only if it meets your requirement (this saves you from creating multiple iterations of test data). Right click on the table in the data generation plan screen and click on Preview Data Generation menu option as shown below and the test data will be shown in a preview screen as shown below.

preview data generation menu option

data generation preview

There are couple of standard and built-in data generators provided (to generate random test data for different data types) which the data generation plan chooses automatically to use depending on the schema of your table or data type of your column. You can change different properties of these data generators associated with each column to define a range or format of data being generated for that column. If this default selection does not suit your requirement, you can change it to use a different data generator than the default. Not only this, you can even create you own custom data generator, to learn more about it click here.

you can change different properties of these data generators associated with each column to define a range or format of data being generated for that column

Also note, if you want test data to be generated by using SQL SELECT commands from a data source, you can use the Data Bound Generator.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, August 16, 2016 - 9:20:06 AM - Ritesh Singh Back To Top (43128)

I am trying to insert data using VSTS 2010 Ultimate and SQL 2008 R2.

I am deploying to my local DB by importing Script from same DB.

While deplying, getting below error:

“Error Deploy01234: The target database schema provider could not be determined.  Deployment cannot continue.”

I have googled it but still error not solved.

Can you please help on this? 

 


Monday, August 17, 2015 - 1:41:25 AM - IrinaCH Back To Top (38456)

This generator may be helpful when you want to fill a database with random data. However, in most cases, this kind of generatorsare suitable only for testing system performance.

Most often you need to work with data that simulate real-world data, such as e-mails, first names, phone numbers, IDs, credit card numbers, etc. In this case, a third-party data generator, such as Devart's dbForge Data Generator for SQL Server, may be immensely useful. This tool includes over a hundred of meaningful generators allowing to generate not only realistic but also valid data with сhecksum support, such as credit card numbers, Social Security numbers, bank account numbers, and much more!


Tuesday, October 2, 2012 - 11:03:17 AM - Anderson Back To Top (19764)

Thank you very much! This is very useful


Thursday, June 21, 2012 - 11:51:19 PM - Amit Yadav Back To Top (18166)

i need to fetch an attribute named customer_id which is autogenerated incremented by 1.

i want to see that customer_id for the next customer for each time when i'll open the customer form,incremented by 1.


Wednesday, August 10, 2011 - 2:14:36 AM - ranjit Back To Top (14355)

Hi
    I have one issue regarding the test data generation.

How to control [number of rows/data] Ratio of possible values in a  column?

For example:

"Visit" table has "Visit Type" with possible values  'InPatient','OutPatient','Emergency'.


I may  want to generate the data in a manner like "20% of generated data should be 'Emergency'" ,

 "60% 'InPatient'" and the rest "40%  'OutPatient'".

Regards,

Ranjeet


Wednesday, January 12, 2011 - 6:58:06 AM - Anil Sharma Back To Top (12586)

I've generated data successfully with independent tables but suppose i have multiple tables with relationship (Parent child relationship)means highly normilize database  kindly suggest how can i generate dummy data or pl sehre me articles or  documents which can help to generate dummy data.


Wednesday, January 12, 2011 - 6:47:04 AM - Anil Sharma Back To Top (12585)

Hi Arshad,

I've generated data successfully with independent tables but suppose i have tables with multiple relationship (Parent child relationship)means highly normilize database  kindly suggest how can i generate dummy data or pl sehre me this king article or pdf document.

I'm trying to generate dummy data but i'm getting error

Regards
Anil Sharma
[email protected]















get free sql tips
agree to terms