Populate main table with random employees (shuffled) from employee table based on department I have a task which I have been working on but am struggling finding the end result.

Task Description
Check each row in Main table


If column a = ‘x’ then assign one of the 4 ‘x’ employees from employee table picked randomly to that row
If column a = ‘y’ then get one of the 4 ‘y’ Standards officers from
employee table picked randomly to that row


Employee Table

Id  Dept Employee
1   x   x1
2   x   x2
3   x   x3
4   x   x4
5   y   y1
6   y   y2
7   y   y3
8   y   y4


Main table (DATA) contains many columns and I need end column (Employee) to be populated by a Employee based on Dept and shuffled in employee table to randomize also.

Thank you
You can use dbatools Invoke-DbaDbDataMasking and have the New-DbaDbMaskingConfig adjusted as per your requirements.

Above has some limitations but it will help achieve what you want to.