Project - Custom SQL Statements

Top  Previous  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 appear like below:


Custom SQL Statement window, this example comes from the Sample Project.


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 data types. Those parameters that are listed as unsupported, either are not detected or return as an unsupported data type. Some database engine support parameter data types, some don't. You can change or override each parameter data type 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 via the Delete rows from table box or execute a custom statement before or after the item is used.


In the above screenshot, for example, all ORDER_LINES with an ID of 99 will be deleted before attempting to process this custom SQL item.


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


In the sample project, a custom INSERT statement is used based on the result of a lookup-query. The statement is executed once, for each row in the result.