/*
  EMDT-425 - remove redundant join table userRolesToRole
  MS SQL Server version

 */
alter table userRolesToRole add user_id int null
go

update urr
SET urr.user_id = ur.user_id
from userRolesToRole urr
       INNER JOIN userRoles ur ON urr.role_userRoleId = ur.userRoles_id


create table userRolesTemp (user_id integer not null, roleId integer not null, primary key (user_id, roleId))
alter table userRolesTemp add constraint FKd8tyo0yeggnxc3c03tt6xelxt foreign key (roleId) references roles (role_id)
alter table userRolesTemp add constraint FKgjefoi8bftew1y9vtbfbm8jsp foreign key (user_id) references users (user_id)
go

INSERT INTO userRolesTemp (user_id,roleId) SELECT user_id,roleId FROM userRolesToRole;

go

DROP TABLE userRolesToRole;
DROP TABLE userRoles;

go

sp_rename 'userRolesTemp', 'userRoles'