Data Generator Project - Custom SQL Statements
Previous  Top  Next

The Advanced Data Generator allows you to use any valid SQL statement when generating data. The statement should include parameters ( :parameter_name ) so that you can supply data to each parameter for each attempt to generate a row of data.

Usually, these will be INSERT INTO statements, but it can also be a call to a stored procedure or other SQL statement.

When creating or modifying a Data Generator Project, you can insert a custom SQL statement via the SQL button on the toolbar. This button is only enabled for projects with a data target of Database.

After that, a dialog will apear like below:

Custom SQL Statement window

Each custom SQL statement should have an unique name (enter it at the Identifier edit box).

Enter the SQL statement in the SQL Statement area. If this area looses focus, the Advanced Data Generator will attempt to detect the parameters and parameter datatypes. Those parameters that are listed as unsupported, either are not detected or return as an unsupported datatype. Some database engine support parameter datatypes, some don't. You can change or override each parameter datatype in the area on the right that says Parameters.

Besides the SQL Statement, there are also options for this "filler item".

The options for a Custom SQL filler item

For your custom SQL statement is used, you can tell the Advanced Data Generator if it should delete the rows for a specific table or execute a custom statement before or after the item is used.

In the above screenshot, for example, all employees with an ID lower than 100 will be deleted before attempting to insert new employees.

When you use the OK button, the item is inserted into the list of normal items and you can use it like any other.

Example SQL statement with "to fill" settings