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
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.