## The question:

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.

##### Deconstructed CTE

```
; 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 `PRJ`

data.

```
10000 101 7
10000 102 10
...
10001 101 7
10001 102 10
...
10002 101 7
10002 102 10
```

##### Question

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
```

##### Why

My goal is to insert these values into another table as test values for each of the `PRJ`

data elements.

## The Solutions:

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.

### Method 1

`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`

cte

```
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