Data generators for SQL server? [closed]

Question

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.

Closed 7 years ago.

I would like to receive suggestions on the data generators that are available, for SQL server. If posting a response, please provide any features that you think are important.

I have never used a application like this, so I am looking to be educated on the topic. Thank you.

(My goal is to fill a database with 10,000+ records in each table, to test an application.)

Answer 1

For generating sample data, I use simple Python applications.

Considerations:

1. Simple to modify and configure.

2. A repeatable set of data that you can for performance testing and get consistent results.

3. Follow all of the DB referential integrity rules and constraints.

4. Realistic data.

The first two indicate that you want to produce script files that will load your data. The third is tougher. There are ways to discover the database metadata and constraints. Looking at 3 and 4 together, you don't want simple reverse engineering -- you want something you can control to produce realistic values.

Generally, you want to build an entity model of your own so that you can be sure you have ranges and key relationships correct.

You can do this three ways.

1. Generate CSV files of data which you can load manually. Nice repeatable test data.

2. Generate SQL scripts which you can run. Nice repeatable data, also.

3. Use an ODBC connection to generate data directly into the database. I actually don't like this as much, but you might.

Here's a stripped-down one-table-only version of a data generator that writes a CSV file.

import csv
import random

class SomeEntity( list ):
    titles = ( 'attr1', 'attr2' ) # ... for all columns
    def __init__( self ):
        self.append( random.randrange( 1, 10 ) )
        self.append( random.randrange( 100, 1000 ) )
        # ... for all columns

myData = [ SomeEntity() for i in range(10000) ]
aFile= open( 'tmp.csv', 'wb' )
dest= csv.writer( aFile )
dest.writerow( SomeEntity.titles )   
dest.writerows( myData )
aFile.close()

For multiple entities, you have to work out the cardinality. Instead of generating random keys, you want to make a random selection from the other entities. So you might have ChildEntity picking a random element from ParentEntity to assure that the FK-PK relationship was correct.

Use random.choice(someList) and random.shuffle(someList) to assure referential integrity.

Answer 2

Visual Studio Team System Database Edition (aka Data Dude) does this.

I have not used it for data generation yet, but 2 features sound nice:

1. Set your own seed value for the random data generator. This allows you to prodcue the same random data more than once.

2. Point the wizard at a 'real' database and have it generate something that looks like real data.

Maybe these are standard features elsewhere?

Answer 3

I've used a tool called Datatect for this.

Some of the things I like about this tool:

1. Uses ODBC so you can generate data into any ODBC data source. I've used this for Oracle, SQL and MS Access databases, flat files, and Excel spreadsheets.
2. Extensible via VBScript. You can write hooks at various parts of the data generation workflow to extend the abilities of the tool.
3. Referentially aware. When populating foreign key columns, pulls valid keys from parent table.

Answer 4

Something similar has been asked here : Creating test data in a database

Red Gate SQL Data Generator does a great job in that domain. You can customize every field of your database and using random data with seeds. And even create specific patterns using Regex expressions.

Answer 5

this one is for free: http://www.sqldog.com contains several functions like: data generator, fulltext search, create database documentation, active database connections

Answer 6

I just found about that one: Spawner