I am trying to load up a table with values that are generated randomly based on another table. The issue is that the result set from the final
Cross Join, is not unique (random) for each of the rows of the final cross joined table.
; with MinMax as -- Step one get the range of values for each `DetailCategoryId` ( select distinct DetailCategoryId, min(DetailId) over(partition by DetailCategoryId) as [Min] , max(DetailId) over(partition by DetailCategoryId) as [Max] from Ref.Detail )
, RandProcess as -- Step two pick a random number between the range for each ( select MM.DetailCategoryId , (MM.[Min] + FLOOR(RAND() * (MM.[Max] + 1 - MM.[Min]))) as Rando from MinMax MM )
The final step is to create a cross reference table by
Cross JOIN that will give the set of
RandProcess into each item. .
select PRJ.ProjectId, RD.DetailCategoryId, RD.Rando from info.Project PRJ CROSS JOIN RandProcess RD
The issue is that each set of data in the
PRJ table is the same. Here is the first two rows of each set
10000 101 7 10000 102 10 ... 10001 101 7 10001 102 10 ... 10002 101 7 10002 102 10
How can I get each of the PRJs sets to be different (random) for each of the PRJ’s rows?
10000 101 7 10000 102 10 ... 10001 101 4 10001 102 11 ... 10002 101 1 10002 102 14
My goal is to insert these values into another table as test values for each of the
PRJ data elements.
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
RAND() is evaluated once and hence you get the same value for all rows.
You can use
newid() to simulate a random value.
checksum() to convert it to integer and
abs() to return absolute value. To get into the your required minimum and maximum range
(ABS(CHECKSUM(NEWID())) % (MM.[Max] - MM.[Min] + 1)) + MM.[Min]
Changes to your
RandProcess as -- Step two pick a random number between the range for each ( select MM.DetailCategoryId , (ABS(CHECKSUM(NEWID())) % (MM.[Max] - MM.[Min] + 1)) + MM.[Min] as Rando from MinMax MM )
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0