RexHelios (4/18/2013)

---

venkyzrocks,

I agree with Sean Lange. More clarity and some ground to get others started would be nice to have.

Having said that, I have created table variables to simulate your scenario. Please check to see if this meets your requirement.

DECLARE @FactTable TABLE (

FiscalID INT, SID INT, Value Float)

INSERT @FactTable

VALUES (1, 344, 78.44), (2, 345, 44.33), (3, 346, 44.55), (4, 347, 223.44)

SELECT * FROM @FactTable

DECLARE @Dim_FiscalYear_Old TABLE (ID INT, FiscalName VARCHAR (20))

INSERT @Dim_FiscalYear_Old VALUES (1, 2008), (2, 2009), (3, 2010), (4, 2011)

SELECT * FROM @Dim_FiscalYear_Old

DECLARE @Dim_Scenario_Old TABLE (ID INT, Name VARCHAR (20))

INSERT @Dim_Scenario_Old VALUES (344, 'Model1'), (345, 'Model2'), (346, 'Model3'), (347, 'Model4')

SELECT * FROM @Dim_Scenario_Old

DECLARE @Dim_FiscalYear_New TABLE (ID INT, FiscalName VARCHAR (20))

INSERT @Dim_FiscalYear_New VALUES (7, 2008), (8, 2009), (9, 2010), (10, 2011)

SELECT * FROM @Dim_FiscalYear_New

DECLARE @Dim_Scenario_New TABLE (ID INT, Name VARCHAR (20))

INSERT @Dim_Scenario_New VALUES (546, 'Model1'), (547, 'Model2'), (548, 'Model3'), (549, 'Model4')

SELECT * FROM @Dim_Scenario_New

UPDATE@FactTable

SETFiscalID = fyn.ID, sid = sn.ID

FROM@FactTable f

INNER JOIN @Dim_FiscalYear_Old fyo ON f.FiscalID = fyo.ID

INNER JOIN @Dim_FiscalYear_New fyn ON fyo.FiscalName = fyn.FiscalName

INNER JOIN @Dim_Scenario_Old so ON f.SID = so.ID

INNER JOIN @Dim_Scenario_New sn ON so.Name = sn.Name

SELECT * FROM @FactTable

- Rex