Make way for a unique key constraint by renaming/updating duplicate rows in SQL Server

If you want to add a unique key constraint or index to a table that might have duplicate records you're in for some fun. You can either delete the duplicates, or fix the data to make them unique.

Suppose you have a table "Widgets", which should be unique on SupplierId and Name, but isn't. This tsql script will update the duplicates by appending a "(1)", "(2)", etc. to the Name, thus satisfying the proposed UKC:

DECLARE @Widgets TABLE(
  Id INT NOT NULL PRIMARY KEY, 
  SupplierId INT NOT NULL, 
  Name NVARCHAR(50) NOT NULL)

INSERT INTO @Widgets (Id, SupplierId, Name)
VALUES (1, 1, 'WidgetA'),
       (2, 2, 'WidgetA'),
       
       (3, 3, 'WidgetB'),
       (4, 3, 'WidgetB'),
       
       (5, 3, 'WidgetC'),
       (6, 3, 'WidgetC'),
       (7, 3, 'WidgetC')

SELECT * FROM @Widgets

;WITH cte AS
(
  SELECT 
    ROW_NUMBER() OVER(PARTITION BY SupplierId, Name ORDER BY Id) AS rno, 
    Name
  FROM @Widgets
)

UPDATE cte SET Name = Name + ' (' + CONVERT(varchar(10), rno) + ')'
WHERE rno>1

SELECT * FROM @Widgets

You ought to be able to run all that and get these results in SQL Server:

Adapt to your needs :).