How to generate the consistent result for multiple days of data.

Nitin

I am using SQL Data generator first time and trying to generate the data for following scenario for stress test. 

Scenario :  I am trying to populate the MasterData in DailyFeedData table for each Business Day. 
BusinessDay_Id and MasterData_Id  are foreign keys. The problem is Generator is populating the data in random fashion i.e. Master_Id 1 generator populating Sec_Code 5100EUKS7 instead of 4100EUKS7. I trying to achieve consistent result as mentioned below "DailyFeedData (Expected result) ".  We have around 100K records in MasterData table and 500 records in BusinessDay table

Not sure if i can achieve this requirement with SQL Data Generator. I know i can achieve this with SQL script. But i want to try with SQL Data Generator. 

Sample Data

Table 1: BusinessDay

--------------------------------------------------------------------
Id            Client_Id       Business_Day            
--------------------------------------------------------------------
1             1001              12/11/2022
--------------------------------------------------------------------
2             1001              12/12/2022
--------------------------------------------------------------------
3             1001              12/13/2022
--------------------------------------------------------------------

Table 2 : MasterData
--------------------------------------------------------------------------------
Id            Sec_Code          Sec_Amount      Sec_Issue_Date         
--------------------------------------------------------------------------------
1             4100EUKS7        1.25                    12/15/2019
--------------------------------------------------------------------------------
2             5100EUKS7        2.05                    02/11/2019
--------------------------------------------------------------------------------

Table 3 : DailyFeedData (Expected result)
----------------------------------------------------------------------------------------------------------------------------------------
Id            BusinessDay_Id       MasterData_Id       Sec_Code          Sec_Amount      Sec_Issue_Date    

----------------------------------------------------------------------------------------------------------------------------------------
1                1                                    1                       4100EUKS7        1.25                    12/15/2019
----------------------------------------------------------------------------------------------------------------------------------------
2                2                                    1                       4100EUKS7        1.25                    12/15/2019
----------------------------------------------------------------------------------------------------------------------------------------
3                3                                    1                       4100EUKS7        1.25                    12/15/2019
----------------------------------------------------------------------------------------------------------------------------------------
4                1                                    2                        5100EUKS7        2.05                    02/11/2019
----------------------------------------------------------------------------------------------------------------------------------------
5                2                                    2                        5100EUKS7        2.05                    02/11/2019
----------------------------------------------------------------------------------------------------------------------------------------
5                3                                    2                        5100EUKS7        2.05                    02/11/2019
----------------------------------------------------------------------------------------------------------------------------------------

Table 3 : DailyFeedData (Actual result)
----------------------------------------------------------------------------------------------------------------------------------------
Id            BusinessDay_Id       MasterData_Id       Sec_Code          Sec_Amount      Sec_Issue_Date    

----------------------------------------------------------------------------------------------------------------------------------------
1                1                                    1                       4100EUKS7        1.25                    12/15/2019
----------------------------------------------------------------------------------------------------------------------------------------
2                2                                    2                       4100EUKS7        2.05                    02/11/2019
----------------------------------------------------------------------------------------------------------------------------------------
3               1                                    2                       4100EUKS7        1.25                    12/15/2019
----------------------------------------------------------------------------------------------------------------------------------------
4                3                                   1                        5100EUKS7        1.25                    02/11/2019
----------------------------------------------------------------------------------------------------------------------------------------
5                2                                    2                       5100EUKS7        2.05                    12/15/2019
----------------------------------------------------------------------------------------------------------------------------------------
5               1                                    1                        5100EUKS7        2.05                    02/11/2019
----------------------------------------------------------------------------------------------------------------------------------------

Thanks,
Nitin

Jon_Kirkwood

Hi @Nitin,

Thank you for reaching out on the Redgate forums regarding your Data Generator query.

Are you using Data Generator just to create data for the DailyFeedData table using existing data in the BusinessDay/MasterData tables?

I am attempting to recreate this scenario and would like to see what settings you may have for generating the data. Are you able to share your SQL Data Compare project file (*.SDC)

This link has been generated to upload your file to so it's not shared over the public forum. The link is unique and active for 2 weeks.
https://files.red-gate.com/requests/z2N6G9rXRrHvm9L3p5fHdg

Nitin

Hi Jon,

I have several tables where i want to generate the data. I have just mentioned one scenario and the data i provided is sample one. Actual table have more columns. As am not using SQL Data compare so i do not have *.SDC file. To generate data using SQL Data Generator tool i have uploaded the SQL GEN files which i have tried with 2 different approaches. 

Jon_Kirkwood

Thank you, confirming I have received the files & your notes.
Attempting to recreate this and come up with some solutions and will update this post with some findings.

Jon_Kirkwood

Hi, I have been working on this one for a while and don't believe the proper output can be generated consistently with Data Generator. Combining the two tables into a single longer table is going to be a concern as it cannot determine the length of the table programmatically. 

In some testing I was able to map BusinessDay_ID to the Id in BusinessDay and MasterData_Id to the Id in MasterData but I needed to enter a length of data to generate. This wasn't always accurate as it could generate null values when trying to match the two tables. 

I did get some measure of success when unchecking the 'Shuffle data' options in my foreign key tables so the Sec_Code, Sec_Amount & Sec_Issue_Date values held some similarities in the DailyFeedData table to MasterData.

Unfortunately I am not 100% sure the full scope of your requirements would be possible through Data Generator and you may need to develop scripts that can be automated to generate this data.