Code for MetroAltDW SSIS

--Drop Keys (foreign then primary)
--Remove Data (truncate)
--Insert data
--reapply keys (primary then foreign)

--drop constraints

   Alter table Factschedule
   drop constraint fk_Dimdate

      Alter table Factschedule
   drop constraint fk_schedule

      Alter table Factschedule
   drop constraint fk_DimRoute

      Alter table Factschedule
   drop constraint fk_Dimbus

      Alter table Factschedule
   drop constraint fk_DimEmployee

      Alter table DimEmployee
   drop constraint Pk_DimEmployee

      Alter table DimBus
   drop constraint Pk_DimBus

      Alter table Dimschedule
   drop constraint Pk_Dimschedule

      Alter table DimRoute
   drop constraint PK_DimRoute

      Alter table DimDate
   drop constraint Pk_Dimdate

   Alter table Factschedule
   Drop constraint Pk_FactSchedule
 
 --truncate tables
 truncate table Dimbus
 truncate table dimdate
 truncate table dimEmployee
 Truncate table dimroute
 truncate table dimschedule
 truncate table factschedule



 --reinsert the live data
  Insert into DimEmployee(EmployeeKey, PositionKey, 
   PositionName, EmployeeHireDate, EmployeeHourlyPayRate)
   Select e.EmployeeKey, p.PositionKey, 
   PositionName, EmployeeHireDate, EmployeeHourlyPayRate
   From MetroAlt.dbo.Employee e
   inner join MetroAlt.dbo.EmployeePosition ep
   on e.EmployeeKey=ep.EmployeeKey
   inner join MetroAlt.dbo.Position p
   on p.PositionKey=ep.PositionKey

  Insert into DimBus(BusKey, BusTypeKey, 
  BustypeDescription, BusTypePurchasePrice)
  Select b.BusKey, bt.BusTypeKey, 
  BustypeDescription, BusTypePurchasePrice
  From MetroAlt.dbo.Bus b
  inner join MetroAlt.dbo.Bustype bt
  on b.BusTypekey=bt.BusTypeKey

 Insert into Dimroute(RouteKey, BusRouteZone)
 Select BusRouteKey, BusRouteZone
 From MetroAlt.dbo.BusRoute

 Insert into dimSchedule(BusDriverShiftKey, 
 BusScheduleAssignmentKey, 
 Employeekey)
 Select BusDriverShiftKey, 
 BusScheduleAssignmentKey, 
 Employeekey
 From MetroAlt.dbo.BusScheduleAssignment

 Insert into DimDate(BusScheduleAssignmentDate, 
 BusScheduleYear, BusScheduleMonth)
 Select distinct BusScheduleAssignmentDate, 
 Year(BusScheduleAssignmentDate),
 Month(BusScheduleAssignmentDate)
 From Metroalt.dbo.BusScheduleAssignment

 Insert into FactSchedule(DimEmployeeKey, 
 DimBusKey, DimrouteKey, DimScheduleKey, 
 DimDateKey, Riders, FaresPerRoute)
 Select Distinct DimEmployeeKey, 
 DimBusKey, DimrouteKey, DimScheduleKey, 
 DimDateKey, Riders, Riders * FareAmount
 From MetroAlt.Dbo.BusScheduleAssignment bsa
 inner join DimEmployee de
 on de.EmployeeKey=bsa.EmployeeKey
 inner join dimBus db
 on db.BusKey=bsa.BusKey
 inner join Dimroute dr
 on dr.RouteKey=bsa.BusRouteKey
 inner join DimSchedule ds
 on ds.BusScheduleAssignmentKey=bsa.BusScheduleAssignmentKey
 inner join DimDate dd
 on dd.BusScheduleAssignmentDate=bsa.BusScheduleAssignmentDate
 inner join MetroAlt.dbo.Ridership r
 on r.BusScheduleAssigmentKey=bsa.BusScheduleAssignmentKey
 inner join MetroAlt.dbo.Fare f
 on f.FareKey=r.FareKey 

 --reapply the constraints
Alter table DimEmployee
Add constraint pk_DimEmployee primary key (DimEmployeeKey)

Alter table DimBus
Add constraint pk_DimBus primary key (DimBusKey)

Alter table Dimroute
Add constraint pk_DimRoute primary key (DimRouteKey)

Alter table DimSchedule
Add constraint pk_DimSchedule primary key (DimScheduleKey)

Alter table DimDate
Add constraint pk_DimDate primary key (DimDateKey)

Alter table FactSchedule
Add constraint pk_FactSchedule 
primary key (DimEmployeeKey, DimBusKey, 
DimRouteKey, DimScheduleKey, DimDateKey)

Alter table factSchedule
add constraint fk_DimEmployee foreign key (DimEmployeeKey)
   References DimEmployee(DimEmployeeKey)

Alter table factSchedule
add constraint fk_DimBus foreign key (DimbusKey)
   References DimBus(DimBusKey)

Alter table factSchedule
add constraint fk_DimRoute foreign key (DimRouteKey)
   References DimRoute(DimRouteKey)

Alter table factSchedule
add constraint fk_Schedule foreign key (DimScheduleKey)
   References DimSchedule(DimScheduleKey)

Alter table factSchedule
add constraint fk_DimDate foreign key (DimDateKey)
   References DimDate(DimDateKey)


Select * from FactSchedule