Create Test Data using Generic SQL Statement with SQL Data Generator

I'm using the test data generator SQL Data Generator in order to create test data in sql database tables for my t-sql tests.
I had recently required a specific test data and find the solution again within Red-Gate SQL Data Generator.
I'm going to try to explain the problem and the solution in order to show you a sample use of SQL Server tool "SQL Data Generator".
I believe this sql guide with creating test data screens from SQL Data Generator will also be a good introduction for this test data tool.

Here is the case which I require test data.
I have an Authors sql database table.
I have a Books sql database table.
Books sql table has a column Authors where the authors of the book is kept as comma seperated values of the author's Id value in the Authors sql table.
I know it sounds a little bit confusing. But I will copy screenshots displaying sample data from both sql database tables which will help you visualize the database design.

SQL Data Generator - tutorial

SQL Data Generator example

As you see, in the Books table in Authors column I keep ID's as comma seperated. I know that is not a good database desing but we have such tables in our databases.
We can just hope creating test data for SQL Server is not so difficult using this tool from Red-Gate.

Since the Authors column in Books table has data type varchar it does not produce logical values if I use the SQL Data Generator tool to populate the row columns with random string values.
I had to customize the test data generation process somehow.
I had to customize the test data generation so that the process should concatenate the AuthorId column values within the BookAuthors table in a random manner for each test data record for the Books table.

We can customize the test data generation by using the "SQL Select Statement" instead of the "RegexpGenerator" default for varchar, nvarchar data types.

SQL Generators List

When you select "SQL Statement - Reads data from SELECT statement" from the list the Column generation settings will change as shown in the below screen-shot from the SQL Server tool.

SQL Data Generator SQL Select Statement

Then click the "Edit" button in order to select the database and edit the t-sql script.
Select the same database since both tables are on the same database. And copy and paste the below sql statements into the "External Source of SQL Data" query screen.

The below script gets a random number of BookAuthors rows and sql concatenate AuthorId column values using comma as delimiter character.
Please refer to following tutorial for more examples on SQL Concatenation using FOR XML PATH() method.


DECLARE @rc int

SELECT @rc = COUNT(*) + 1 FROM BookAuthors



DECLARE @i int

SELECT @i = CAST(RAND() * 1.0 * @rc as int)



SELECT

  STUFF(

  (

    SELECT TOP (@i)

      ',' + CAST(AuthorId as varchar(3))

    FROM BookAuthors

    ORDER BY NEWID()

    FOR XML PATH('')

  ), 1, 1, ''

) As concatenated_string

SQL Code

Then click Finish to go to next step.
The sql tool will return the user to the same screen back.
Do not forget to check the "Loop until number of rows required is reached" checkbox.
Be sure of that it is marked. Otherwise the above script will only produce test data for single row.

SQL Data Generator tutorial

Here is the test data the tool SQL Server data generator populated for sql developers and database administrators like us.
I hope you find this short SQL Data Generator tutorial in order to create test data using custom code useful.