personally I would not try to insert records into your table.

see if this gets anywhere close to what you require....could well be improved I expect.

declare @TP as INT

set @TP = 9999

;WITH

cteOrg

AS ( SELECT C.OrgNum AS Bank ,

B.OrgNum AS Region ,

A.OrgNum AS Branch

FROM

dbo.OrgHierarchy AS A INNER JOIN dbo.OrgHierarchy AS B ON A.ParentOrgNum = B.OrgNum

INNER JOIN dbo.OrgHierarchy AS C ON B.ParentOrgNum = C.OrgNum ) ,

cte_allGoals

AS ( SELECT dbo.GoalDefs.INum ,

dbo.GoalDefs.GoalNum ,

cteOrg.Branch ,

@TP AS TP

FROM

dbo.GoalDefs CROSS JOIN cteOrg ) ,

cte_Results

AS ( SELECT AG.INum ,

AG.GoalNum ,

AG.Branch ,

AG.TP ,

ISNULL( SUM( RH.ResultItems ) , 0 )AS ResultItems ,

ISNULL( SUM( RH.ResultValue ) , 0 )AS ResultValue

FROM

cte_AllGoals AS AG LEFT OUTER JOIN dbo.ResultsHistory AS RH ON AG.TP = RH.TimePeriod

AND AG.Branch = RH.OrgNum

AND AG.GoalNum = RH.GoalNum

AND AG.INum = RH.INum

GROUP BY AG.INum ,

AG.GoalNum ,

AG.Branch ,

AG.TP )

SELECT cte_Results.INum ,

cte_Results.GoalNum ,

cteOrg.Bank AS OrgNum ,

SUM( cte_Results.ResultItems )AS RI ,

SUM( cte_Results.ResultValue )AS RV ,

cte_Results.TP

FROM

cteOrg INNER JOIN cte_Results ON cteOrg.Branch = cte_Results.Branch

GROUP BY cte_Results.INum ,

cte_Results.GoalNum ,

cteOrg.Bank ,

cte_Results.TP

UNION ALL

SELECT cte_Results.INum ,

cte_Results.GoalNum ,

cteOrg.Region AS OrgNum ,

SUM( cte_Results.ResultItems )AS RI ,

SUM( cte_Results.ResultValue )AS RV ,

cte_Results.TP

FROM

cteOrg INNER JOIN cte_Results ON cteOrg.Branch = cte_Results.Branch

GROUP BY cte_Results.INum ,

cte_Results.GoalNum ,

cteOrg.Region ,

cte_Results.TP

ORDER BY OrgNum , cte_Results.GoalNum;