SQL Server : ROW_NUMBER() OVER on table variable to amend duplicate columns

Lets say I have table variable declared like so...

DECLARE @LocalTable TABLE
(
    IdField NVARCHAR(MAX),
    NameField NVARCHAR(MAX)
)

And I populate it like so...

INSERT INTO @LocalTable
SELECT
    IdColumn,
    NameColumn
FROM SourceTable

NameColumn in the source table may have duplicate values, and therefore NameField in the local table will have the same duplicate values.

And let's say I want to insert the local table into a target table like so...

INSERT INTO TargetTable (NewIdColumn, NewNameColumn)
    SELECT
        IdField,
        NameField
    FROM 
        @LocalTable

BUT: NewNameColumn in TargetTable has a UNIQUE constraint, and so duplicates cause an exception.

I want to apply this example,

ROW_NUMBER() OVER(PARTITION BY NameField ORDER BY NameField)

Such that the NameField is appended/suffixed with a number digit indicating its duplication.

I have this working example that can select correctly appended values, but I cannot get this to work in an update statement like this:

UPDATE localtable 
SET NameField = AppendedNameField 
FROM @LocalTable AS localtable

SELECT 
    CONCAT(Ref.NameField, ROW_NUMBER() OVER (PARTITION BY Ref.NameField 
                                             ORDER BY Source.IdField)), *
FROM 
    @LocalTable AS Source
INNER JOIN 
    @LocalTable AS Ref ON Ref.NameField = Source.NameField  
                       AND Ref.IdField != Source.IdField

Thanks in advance.

Solution

If I have understood what you are trying to do.

WITH CTE AS
(
SELECT 
CONCAT(NameField, ROW_NUMBER()
    OVER(PARTITION BY NameField ORDER BY IdField)) AS NewName, *
FROM @LocalTable
)
UPDATE
CTE SET Name = NewName

If you only want to do it to duplicated names you can add a COUNT(*) OVER (PARTITION BY Name) into the CTE and conditional logic using that.