Help for solving a problem in a SQL Server assignment

Question

Hi, I have created the following code so as to insert into two tables Users and Roles:

-- to insert into the Users table

declare @u int
set @u = 1000

while @u <= 1200
begin
   insert into Users(UserID, Username, [Password], Email)
   values(@u, 
         'Username'+CAST(@u as varchar(4)),
         'pass'+CAST(@u as varchar(4)),
         'Email'+CAST(@u as varchar(4)))

   set @u = @u + 1
end
--to insert into the Roles table
declare @a int
set @a = 1

while @a <= 100
begin
  insert into Roles (RoleID, [Role], [Description])
  values(@a, 'Admin', 'description’ + CAST(@a as varchar(4)))

  set @a = @a + 1
end

Now I want to do the same for a table named ‘User_Roles’, which get has two foreign key one from Users table and other from Roles. I have written the following:

declare @b int
set @b = 1

while @b <= 300
begin
  insert into User_Roles(UserID, RoleID, [Description])
    select  
       UserID, RoleID, 'Description' + CAST(@b as varchar(4))
    from Users, Roles
    where UserID = CAST((RAND()*200+1000) as int)
      and RoleID = CAST((RAND()*99+1)as int)

  set @b = @b + 1
end

The two first code will be executed correctly, but the last one cause an error because it will insert duplicate values in the primary key. How can I change the last part and solve this problem?

Answer 1

Modify your INSERT statement to include a GROUP BY, to eliminate UserID and RoleID duplicates.

 insert into User_Roles(UserID, RoleID,[Description])
     select  UserID, RoleID,'Description'+CAST(@b as varchar(4))
     from Users, Roles
     where  UserID = CAST((RAND()*200+1000) as int)
     and    RoleID = CAST((RAND()*99+1)as int)
     GROUP BY UserID, RoleID 

Answer 2

Each time through the loop, you have to ensure that you are inserting a value that doesn't already exist.

Untested, but something like this should work:

  insert into User_Roles(UserID, RoleID, [Description]) 
    select   
       u.UserID, r.RoleID, 'Description' + CAST(@b as varchar(4)) 
    from Users u, Roles r
    where u.UserID = CAST((RAND()*200+1000) as int) 
      and r.RoleID = CAST((RAND()*99+1)as int) 
      and NOT EXISTS(SELECT 1 from User_Roles ur WHERE ur.UserID = u.UserID AND ur.RoleId = r.RoleId)