/*
 EMDT-584
 MS SQL Server script to remove joining table SENIOR_STAFF_INFO, fix SUPPORTING_STAFF / Users relationship and migrate data
 Paul Barratt
 02 August 2019
 */
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'SUPPORTING_STAFFS_TEMP' AND TABLE_SCHEMA = 'dbo')
    drop table dbo.SUPPORTING_STAFFS_TEMP;
go
alter table SUPPORTING_STAFFS drop constraint FKos6wn8gval5w6pg5eiiac1t94
go
create table SUPPORTING_STAFFS_TEMP (SENIOR_STAFF_ID integer not null, SUPPORTING_STAFF_ID integer not null, primary key (SENIOR_STAFF_ID, SUPPORTING_STAFF_ID));
alter table SUPPORTING_STAFFS_TEMP add constraint FKnny4fvxiuvvxy7y7u378s7ya foreign key (SUPPORTING_STAFF_ID) references users (user_id);
alter table SUPPORTING_STAFFS_TEMP add constraint FKos6wn8gval5w6pg5eiiac1t94 foreign key (SENIOR_STAFF_ID) references users (user_id);
go
insert into SUPPORTING_STAFFS_TEMP (SENIOR_STAFF_ID, SUPPORTING_STAFF_ID) (select ssi.seniorStaffUser_id, ss.SENIOR_STAFF_ID from SENIOR_STAFF_INFO ssi join SUPPORTING_STAFFS ss on ssi.id = ss.USER_ID)
go
alter table SUPPORTING_STAFFS drop constraint FKq294sdm7kt21k9o190u38y4ja
go
drop table SENIOR_STAFF_INFO
drop table SUPPORTING_STAFFS
go
EXEC sp_rename 'SUPPORTING_STAFFS_TEMP' , 'SUPPORTING_STAFFS'
go
