ErcanOPAK.com

--STEP 1: Find Repetitive Records (Only the Active Ones)
SELECT INDEX1, INDEX2
INTO #TEMP_TABLE
FROM MY_TABLE
WHERE ACTIVE = 1
GROUP BY INDEX1, INDEX2
HAVING COUNT(1) > 1
ORDER BY COUNT(1) DESC

--STEP 2: Create the Cursor
DECLARE @INDEX1 INT
DECLARE @INDEX2 INT
DECLARE @ID INT
DECLARE MY_CURSOR CURSOR FOR
                                                                               
SELECT INDEX1, INDEX2 FROM #TEMP_TABLE

OPEN MY_CURSOR

--Be careful about the order of indexes
FETCH NEXT FROM MY_CURSOR INTO @INDEX1, @INDEX2

WHILE @@FETCH_STATUS = 0
                BEGIN
                
                               --STEP 3: Choose an active record from the repetitive ones
                               SELECT TOP(1) @ID = ID FROM MY_TABLE
                               WHERE INDEX1 = @INDEX1 AND INDEX2 = @INDEX2 AND ACTIVE = 1

                               --STEP 4: Instead of deleting a record, I always choose to update it as Inactive
                               UPDATE MY_TABLE
             SET ACTIVE = 0
                               WHERE INDEX1 = @INDEX1 AND INDEX2 = @INDEX2 AND ID != @ID

                               PRINT 'The repetitive records have been deleted.'
                               FETCH NEXT FROM MY_CURSOR INTO @INDEX1, @INDEX2

                END

CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR

DROP TABLE #TEMP_TABLE