Assign ID based on distinct column value

Question

I m pulling data from a table into a temp table which has different codes and looks like:

>CODE
>
>"notified",DONE
>
>"info",DONE
>
>DONE
>
>DELAY
>
>"action",DELAY
>
>INFO
>
>DAMAGED
>
>"2_items",DAMAGED

I m manipulating it to get rid of the auxiliary information by:

SUBSTRING(CT.latest_status_code, CHARINDEX(',',CT.latest_status_code) + 1, LEN(CT.latest_status_code)) as 'Code'

and end up with

>DONE
>
>DONE
>
>DONE
>
>DELAY
>
>DELAY
>
>INFO
>
>DAMAGED
>
>DAMAGED

Now, I m trying to assign an ID for each distinct code after removing the auxliary info, something like

>**Code ID**
>
>DONE 1
>
>DONE 1
>
>DONE 1
>
>DELAY 2
>
>DELAY 2
>
>INFO 3
>
>DAMAGED 4 
>
>DAMAGED 4

I ve created another table and I m trying to add the IDs with:

DENSE_RANK() OVER (ORDER BY Code) as 'Code_Key'

However, I get 2 different IDs for the same code, I think it s mostly because I concatenating the string earlier. See below:

>**Code ID**
>
>DONE 1
>
>DONE 2
>
>DONE 3
>
>DELAY 4
>
>DELAY 5
>
>INFO 6
>
>DAMAGED 7
>
>DAMAGED 7

What s the best method to overcome this?

Thank you

Whole Query

IF OBJECT_ID ('tempdb..#Code_Keys') IS NOT NULL DROP TABLE #Code_Keys
CREATE TABLE #Code_Keys (
                            Prod int
                            ,Code varchar(1000)
                            ,updated_date datetime
                            ,code_key int
                        )
INSERT INTO #Code_Keys(Prod, Code, updated_date, code_key)
SELECT 
        x.Prod
        ,x.Code
        ,x.updated_date
        ,DENSE_RANK() OVER (ORDER BY Code) as 'Code_Key'
FROM (
            SELECT
                    Prod
                    ,SUBSTRING(CT.latest_status_code, CHARINDEX(',',CT.latest_status_code) + 1, LEN(CT.latest_status_code)) as 'Code'
                    ,updated_date
            FROM #tempdb CT
        ) x

Answer 1

It looks like the query I posted was doing exactly what I wanted after refreshing the temp tables.

Thanks @SalmanA for giving insights on windows functions and will keep in mind the trick you ve shown.

Hope this helps someone else.