use EnVisage

/*--CHECK SYSTEM PROJECTS - DON'T WE MISS ANYTHING?
select * from project p
left join [Status] st on st.Id = p.StatusId
left join [Type] t on t.Id = p.TypeId
where t.IsSystem = 1 or st.IsSystem = 1
*/

--select projects w/o actuals scenarios
select p.Id, p.Name 
into #tmp
from Project p 
left join [Type] t on t.Id = p.TypeId
left join [Status] st on st.Id = p.StatusId
left join Scenario s on s.ParentId = p.id and s.Type = 9
where p.HasChildren = 0 and t.IsSystem = 0 and st.IsSystem = 0
group by p.id, p.Name
having count(s.Id) = 0

--iterate through #tmp and create actuals scenarios
declare @projectId uniqueidentifier
declare actcursor cursor for select Id from #tmp
open actcursor
fetch next from actcursor into @projectId
while @@FETCH_STATUS = 0
begin
	insert into Scenario(Id, ParentId, Type, Name, ProjectedRevenue, StartDate, Color, FreezeRevenue, GrowthScenario) 
	values (newid(), @projectId, 9, 'ACTUALS', 0, getdate(), '', 0, 0)
	print 'project ' + cast(@projectId as nvarchar(50)) + ' - actuals scenario added'
	fetch next from actcursor into @projectId
end
close actcursor;
deallocate actcursor;
drop table #tmp