SQL Server: ROW_NUMBER () OVER on the Table Variable to Change Duplicate Columns

advertisements

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.


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.