TransferFSNEPtoFSNEP2.sql

IF object_id('tempdb..#records') IS NOT NULL
BEGIN
   DROP TABLE #records
END

CREATE TABLE #records
(
   new_id int IDENTITY(1,1),
   old_id int,
   [type] char(1),
   [month] int,
   [year] int,
   userid uniqueidentifier,
   statusid int,
   reviewComment varchar(512),
   salary float,
   adjustment float,
   adjustmentComment varchar(512)
)

IF object_id('tempdb..#entries') IS NOT NULL
BEGIN
   DROP TABLE #entries
END

CREATE TABLE #entries
(
   new_id int IDENTITY(1,1),	-- new entry id
   old_id int,					-- old entry id
   [type] char(1),
   new_record_id int,			-- the new id of the sheet it should be associated with
   fundtypeid int,
   projectid int,
   financeid int,
   comment varchar(256),
   [date] int, [hours] float, activitytypeid int, adjustmentdate datetime,	-- timesheet entries
   expensetype int, expenseamount int, [description] varchar(128)			-- expense sheet entries
)

-- transfer the information from the time/expense sheets
begin transaction
begin try
	
	insert into #records (old_id, [type], [month], [year], userid, statusid, reviewComment, salary, adjustment, adjustmentComment)
	select ID, 't', [month], [year], userid, statusid, reviewcomment, salary, adjustment, adjustmentComment
	from timesheets

	insert into #records (old_id, [type], [month], [year], userid, statusid, reviewComment)
	select ID, 'e', [month], [year], userid, statusid, reviewcomment
	from ExpenseSheets	
	
	insert into #entries (old_id, [type], new_record_id, fundtypeid, projectid, financeid, comment, [date], [hours], activitytypeid,adjustmentdate)
	select ID, 't', r.new_id, fundtypeid, projectid, financeaccountid, comment, [date], [hours], activitytypeid, adjustmentdate
	from timesheetentries tse
		inner join #records r on tse.TimeSheetID = r.old_id and r.type = 't'
	
	insert into #entries (old_id, [type], new_record_id, fundtypeid, projectid, financeid, comment, expensetype, expenseamount, [description])
	select ID, 'e', r.new_id, fundtypeid, projectid, financeaccountid, comment, expensetypeid, expenseamount, [description]
	from ExpenseSheetEntries ese
		inner join #records r on ese.ExpenseSheetID = r.old_id and r.type = 'e'

	set identity_insert fsnep2.dbo.records on

	insert into FSNEP2.dbo.Records (ID, [Month], [Year], UserId, StatusID, ReviewComment)
	select new_id, [month], [year], userid, statusid, reviewComment
	from #records
	
	-- copy expense into the cost share records table
	insert into FSNEP2.dbo.CostShareRecords (ID)
	select new_id from #records where [type] = 'e'
	
	-- copy expense into the time sheet records table
	insert into FSNEP2.dbo.TimeRecords(ID, Salary)
	select new_id, salary from #records where [type] = 't'
	
	set identity_insert fsnep2.dbo.records off
	
	set identity_insert fsnep2.dbo.entries on
	
	insert into FSNEP2.dbo.Entries (ID, recordID, fundtypeid, projectid, financeaccountid, comment)
	select new_id, new_record_id, fundtypeid, projectid, financeid, comment
	from #entries
	
	-- copy info to timerecordentries table
	insert into FSNEP2.dbo.TimeRecordEntries (ID, [Date], [Hours], ActivityTypeID, AdjustmentDate)
	select new_id, [date], [hours], activitytypeid, adjustmentdate
	from #entries
	where [type] = 't'
	
	-- copy info to costshareentries table
	insert into FSNEP2.dbo.CostShareRecordEntries(ID, ExpenseTypeID, ExpenseAmount, [description], Exclude)
	select new_id, expensetype, expenseamount, [description], 0
	from #entries
	where [type] = 'e'
	
	set identity_insert fsnep2.dbo.entries off

	-- insert the tracking information
	insert into FSNEP2.dbo.RecordTracking (RecordID, StatusID, ActionDate, UserName)
	select new_id, st.statusid, st.ActionDate, st.UserId
	from SheetTracking st
		inner join #records r on st.TimesheetID = r.old_id and r.[type] = 't'
	where TimesheetID is not null and ExpenseSheetID is null

	insert into FSNEP2.dbo.RecordTracking (RecordID, StatusID, ActionDate, UserName)
	select new_id, st.statusid, st.ActionDate, st.UserId
	from SheetTracking st
		inner join #records r on st.ExpenseSheetID = r.old_id and r.[type] = 'e'
	where TimesheetID is null and ExpenseSheetID is not null
	
	commit transaction	
end	try
begin catch
	rollback transaction

--Clean up and make sure the identity inserting is off
set identity_insert fsnep2.dbo.records off
set identity_insert fsnep2.dbo.entries off

  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)


end	catch


DROP TABLE #records
DROP TABLE #entries