Craig's idea sounds great!
Before I saw it, I came up with this , which is similar but different to Wayne's method for tying the rows together...
Just including it as an option, but probably Craig's SSIS magic is much better as this does some horrible sorting operations :sick:
;with magic as
(
select
st.ID
, st.Extract_Record
, st.UNID
, case left(st.Extract_Record,1)
when '2' then rank() over(partition by left(st.Extract_Record,1) order by st.id)
else row_number() over(order by st.id) - rank() over(partition by left(st.Extract_Record,1) order by st.id)
end AS new_ID
from staging AS st
where st.Extract_Record like '[23]%'
)
update magic
set unid = new_ID
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);