Adding text to a field to make it unique

Question

I am using SQL Server 2000, I have a situation where I am copying data over from one table to another, the destination data table requires each Name row to be unique. Here is a quick example of my issue

Source table
RowID | Name
1       A
2       B
3       B
4       B
5       C
6       D
7       C

What I want to do is turn it in to this

Destination table
RowID | Name
1       A
2       B
3       B(2)
4       B(3)
5       C
6       D
7       C(2)

The Name column is a varchar(40), any idea on how do to it, I have 2561 rows that have duplicates so doing it by hand is not a option.

Any ideas on where to begin?

Answer 1

If it's a 1-time deal and you're going to create a unique constraint when you get done:

create a temp table select name from table group by name having count(*) > 1

set rowCount=1 update base table join temp table on temp.name=base.name set name = name + '(1)'

repeat until done remove all (1) entries

Sorry you'll need to write the real SQL yourself. If you had SQL2K5 you could use Row_Number() to do this.

Answer 2

You're going to need a cursor.

Something like below:

CREATE TABLE TempTable ( RowID INT IDENTITY PRIMARY KEY, SomeValue varchar(10))
INSERT INTO TempTable (SomeValue) VALUES( 'A')
INSERT INTO TempTable (SomeValue) VALUES( 'B')
INSERT INTO TempTable (SomeValue) VALUES( 'B')
INSERT INTO TempTable (SomeValue) VALUES( 'B')
INSERT INTO TempTable (SomeValue) VALUES( 'C')
INSERT INTO TempTable (SomeValue) VALUES( 'C')
INSERT INTO TempTable (SomeValue) VALUES( 'D')
INSERT INTO TempTable (SomeValue) VALUES( 'D')
INSERT INTO TempTable (SomeValue) VALUES( 'D')
INSERT INTO TempTable (SomeValue) VALUES( 'D')


CREATE TABLE #Counts (SomeValue varchar(10), ValCount int CONSTRAINT COunts_Unique UNIQUE(SomeValue))
INSERT INTO #Counts(SomeValue, ValCount)
SELECT DISTINCT SomeValue, 0 FROM TempTable

DECLARE @RowID int
DECLARE @SomeValue VARCHAR(10)
DECLARE @ValCount int
DECLARE curs CURSOR for SELECT RowID, SomeValue FROM TempTable ORDER BY RowID ASC
OPEN curs
FETCH NEXT FROM curs into @RowID, @SomeValue
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SELECT @ValCount = ValCount FROM #Counts WHERE SomeValue = @SomeValue
    IF(@ValCount > 0)
    BEGIN
        UPDATE TempTable 
        SET SomeValue = SomeValue + '(' + Convert(varchar, @valCount) + ')'
        WHERE RowID = @RowID
    END

    UPDATE #Counts SET ValCount = ValCount + 1 where SomeValue = @SomeValue

    FETCH NEXT FROM curs into @RowID, @SomeValue
END
CLOSE curs
DEALLOCATE curs

DROP TABLE #Counts

Answer 3

I have decided that I do not need it starting at 1 every time so I just decided to copy the contence of the row ID column at the end of the product.

update #Inv
set name = left(rtrim(name), 40-len(RowId)-1) + ' ' + RowId
where name in (SELECT distinct name
               FROM [#Inv] a
               WHERE exists (select [name] from [#Inv] where not [RowId] = a.[RowId] and [name] = a.[name]))