I have 3 tables where I want to generate fake data in UsersCategoryLink
.
How can I insert in the table UserCategoryLink
the column UserID
with random users from the table User
and a random id from the table Categories
. In this SQL fiddle you can see the tables with some values.
UsersCategoryLink
must be filled with random users and categories.- Every user must have two categories.
UserID
andCategoryID
are primary keys, so every value must be unique.- I am using SQL Server Express.
Best Answer
This is my version of the cross-reference table where you'll store users and the categories they are members of. Notice there is a primary key clustered index on
(UserID, CategoryID)
; this ensures each row is unique:Since you already have data in the
Users
andCategories
tables, you can use a CTE with theROW_NUMBER
windowing function partitioned byUserID
and ordered by an essentially random value,NEWID()
. This allows us to pick precisely two "categories" for each "user":The following is a sample of rows I generated on my system: