SQL Data Generator provides the following generators in the Generic category for you to customize:
Information about each of these generators is provided below.
For information about how to customize the generators, see Customizing existing generators.
Use the CSV generator when you want to import data from a CSV file into a single column. (If you want to import data from a CSV file into an entire table or multiple columns in a table, you can use the Use existing data source table generation setting instead; for details, see Mapping CSV files.)
Click Browse to select the CSV file you want to use; you then specify the delimiters to be used when importing the data, and select the column in the CSV file that you want to import.
File Import generator
Use the File Import generator to import the contents of files in a specified folder.
For example, if you specify a folder containing a number of images, each image is imported into a new row. You can specify a search string to identify the files within the specified folder you want to use.
If you specify large files, or if you specify a large number of files, performance will be reduced.
File List generator
Use the File List generator to import values from a text file.
You must first create a text file containing the list of values, with each value on a new line. The values will be imported from the list in a random order. You can then browse to this file when you select the File List generator.
If you have a very long list of values, you may want to consider creating a CSV file with the list of values and then importing the values using the CSV generator to import the values.
Python script generator
Use the Python script generator to define the generated data using an IronPython script.
A script must define a
main() function that takes a single
config is a dictionary that contains values you can use in your scripts. Values are specified for the following keys:
|The name of a column||First Name|
|The column's data type|
|The column's size (for char columns)||40|
|The number of rows to generate||1000|
|The generator's seed value||10|
|The uniqueness of a column||True|
The connection string for the database
|Data Source=(local);initial catalog="ADatabase";Integrated security=SSPI;|
The path to the installed Generators folder
|C:\Program Files (x86)\Red Gate\SQL Data Generator 3\Generators|
The path to the installed Config (External files) folder
|C:\Program Files (x86)\Red Gate\SQL Data Generator 3\Config|
|The path to the user-defined generators folder||C:\Users\an.other\AppData\Roaming\Red Gate\SQL Data Generator\Config|
For information on how to write Python scripts, see the Python tutorial (Python documentation).
For a list of example scripts you can use with SQL Data Generator, see Example Python scripts.
Use the Regexp generator to define the generated data using a regular expression.
In the basic syntax, most characters are treated as literals (for example, a generates "a"). Below is a list of syntax elements.
$"file_name" to import a text file containing a list of values.
$[column_name] to import the values from another column in the same table.
You can use the buttons below the Regular Expression box to add commonly-used components for a regular expression, file lists, and table columns.
Simple expression generator
With the Simple expression generator, you can write a one line expression to generate data. An expression can use:
- data from other columns in the table
- Python functions
- a set of C# DateTime methods
You can use Python functions with the Simple expression generator, including:
You can also use the following C# DateTime methods with the Simple expression generator:
Eligibile to vote
This expression uses a table's BirthDate column to determine if a person is eligible to vote:
The expression generates the values True or False.
Add days to date
This expression adds 100 days to values in a Date column:
Exam pass or fail
This expression uses a table's ExamScore column to determine if a student has passed or failed an exam:
The expression generates the values Pass or Fail.
Offset time using fixed intervals
This expression offsets values from a table's BookingTime column by intervals of 20 minutes between 6am and 9am:
SQL Statement generator
Use the SQL Statement generator to define data to import from an external database using a SQL statement. If you want to import data from an external database into a an entire table or multiple columns in a table, you can use the Use existing data source table generation setting instead; for details, see Mapping SQL tables or views.
Click Edit to specify the SQL Server and database, and then click Next to enter the SQL statement. The SQL statement must select a single column of values which are of the correct data type.
If the column you are populating has a unique constraint, make sure the SQL statement returns unique values; if it does not, the data generation will fail.
Text Shuffler generator
Use the Text Shuffler generator when you want to create values that contain words randomly selected from a pre-defined list. For example, you may want to use this to check the performance of a full text index.
You can type the text, or you can import it from a text file. You are recommended to use text that contains a high variety of words that are similar to your real data.
SQL Data Generator shuffles the text using the spaces as delimiters for the words. Therefore, if there is punctuation in the text, this will be included in the values.
Weighted List generator
Use this generator when you want to specify the percentage for the number of occurrences of each value in the column. For example, you may want to use this to check that your indexing strategy will work on the table.