Specifying Formatted Data

Different format characters are used to specify formatted data. The information in this section defines these formats.

Note: The information in this section applies only if you selected Formatted Data as your data source in the previous section.

String Formats

The following table specifies the rules for specifying format for strings:

 

Keyword

Description

/

Escape character

#

Numeric values

$

Uppercase characters only

^

Lowercase characters only

@

Alphanumeric with uppercase letters

%

Alphanumeric with lowercase letters

~

Alphanumeric with mixed case letters

*

This character MUST appear at the end, and it means that one or more characters can appear on the right side of the generated string

Cached records

When records are read from either a table or text file, WinSQL Professional reads a certain amount of rows in the memory. These records are called cached records. The default number of records in cache is 1000. Once enough records are generated, the program discards all the rows and refreshes the cache.

Percentage per cache

This is the percentage of total records that should be assigned from a batch of cached records.

The following figure depicts the concept graphically:

In the above example, we have to insert 1000 records in the target table, and there are 1750 records in the input file/table. The specified percent cache is 40%, which means that WinSQL Professional will read 1000 rows from the table or text file and randomly pick records 400 values. Then, WinSQL Professional discards the cache data and fills it again with new values. However, the second time this operation is performed, there are only 750 records left in the input source. Therefore, the remaining 60% of the data will be created using the 750 records that are in the cache.

Notice that the first field in the example, cust_id, is an AGN (automatically generated number), for which the database is responsible for assigning values.  Synametrics recommends that you not change the value for this field.

For the purposes of this example, a sample data file (SDF) is used as the data source.  A few sample SDF files come with WinSQL Professional and are located in the folder where WinSQL Professional is installed.

1          Click the first_name field.

2          On the right side of the window, click Read from a text file.

3          Click Browse to select FirstName.sdf.

4          Repeat steps 5-7 for the last_name field, selecting LastName.sdf as the file name.

5          Click the city field.

6          On the right side of the window, click Read from a text file.

7          Click Browse to select citiState.sdf.

8          Repeat steps 9-11 for the state and zip fields, using 2 and 3 for token numbers. Select the citiState.sdf file for the state and zip fields. This file contains fields for 3 columns.

9          Click the phone field.

10          On the right side of the window, click Formatted data.

11          Select US Telephone Numbers from the list that is displayed. If additional formatting options are desired, select them from the list that is displayed.

12          Click Next.  The following window displays:

13          In the Number of records to be generated field, enter the number of records you wish to generate.  There is no record number limit.

Note: If the input source is a text file for any field and includes a sequential flag, you must have enough records in the SDF, or WinSQL Professional will restart from the beginning of the last batch and will create duplicate records.

Running insert query within a transaction

Data can be generated within a transaction. This allows you to ROLLBACK in case of any errors. By default, this option is disabled. Before enabling this option, you must refer to your database documentation regarding transaction support. Certain databases, such as Informix, allow you to temporarily disable transactions, in which case you will not be able use this feature.

After a specified number of records, run the following query

This feature allows you to run a query after a certain number of records are generated. This could be useful if you need to backup the transaction log so that more records can be inserted. Leave this field blank if you do not wish to run any query.

Repeat steps 1-17 for the employee and products tables.

Populating secondary tables

Once all the primary tables are populated, data to satisfy foreign keys relationships in the secondary tables is now present. Follow the same steps to generate data for these tables, noting that you can now select a table as an input source. If your ODBC driver supports foreign key functionality, WinSQL Professional automatically selects the appropriate input source that will point to a primary key in another table.

In our example, lineItem is the last table in the dependence-hierarchy since it depends on the orders table. Therefore, the next candidate for data generation should be the orders table.

Ignoring a field

If you need WinSQL Professional to ignore a field for any particular reason, simply double-click the column name in the list box. This will cross out the field name and display it in gray text.

Although ignoring a field is almost like setting the NULL percentage to 100, there can be situations where they can differ. Consider the following example:

You have a table that is defined as:

create table myTable(

id          autoNumber primary key,
          name          varchar(30),
          phone          varchar(15)
)
 

In the case where the name field is ignored, WinSQL Professional will generate the following code:


insert into myTable(phone)
values("555-1212")

However, if the field is set to produce 100% NULL values, the generated SQL will look like:

insert into myTable(name, phone)
values(NULL, "555-1212")

In the above example, it does not matter if the name field is ignored or set to NULL. The output is the same in either case. However, if the table definition looked like following, there would be a difference:

create table myTable(

id          autoNumber primary key,
          name          varchar(30),
          phone          varchar(15) default '555-1212'