Yo Jeff, sounds like a candidate for the "Quirky Update"?

Robyn Page's Article[/url]

Jeff Moden's Article[/url]

I added a second UNID column and ran this:

DECLARE @RollingCounterLevel1 int

, @RollingCounterLevel2 int

UPDATE Staging

SET @RollingCounterLevel1 = UNID = CASE LEFT(Extract_Record, 1) WHEN '1' THEN ID ELSE @RollingCounterLevel1 END

, @RollingCounterLevel2 = UNID2 = CASE LEFT(Extract_Record, 1) WHEN '1' THEN NULL WHEN 2 THEN ID ELSE @RollingCounterLevel2 END

It gave me the correct 1st and 2nd level IDs, you might need to tweak it depending if you want the 2nd level value to be itself or the 1st level value.

As I remember there are several prerequisites to make sure the Quirky Update works (hints, parallelism, indexes, etc.) and MS doesn't "guarantee" it will always work, but Jeff couldn't find a situation where it didn't work as he expected.

Chad