I'm trying to figure out how to assign the original parent id from records that have an id linking back (IdXref) to the previous. This can be an unknown number of levels deep. As shown in the desired results below, id 5 links back to 3, and 3 then links back to 1, so the master parent id for 5 would be 1, and so on. create table #Data (Id int null, IdXref int null) insert #Data (Id, IdXref) select 1, null union all select 2, 1 union all select 3, 1 union all select 4, 1 union all select 5, 3 union all select 6, 3 union all select 7, 6 union all select 8, null union all select 9, null union all select 10, 9 /* Desired final table data: Id Xref MasterParent 1 1 2 1 1 3 1 1 4 1 1 5 3 1 6 3 1 7 6 1 8 8 9 9 10 9 9 11 10 9 */ The table that I am adding the MasterParent column to is very large. When records are added to the table, i understand I need to traverse back through the entire table until i find the MasterParent; however, I do not want to find and update the MasterParent for every single record, only those that are new that havent been assigned the MasterParent. I would also need to update those child records that had previously been assigned a MasterParent (most likely the xref since the parent record did not exist in the table yet) if matching parent records were loaded at a later date. Any suggestions?