--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
SQL 'string_split()' function
How to find the first missing value in a series in MS SQL
Replace duplicate spaces with a single space in SQL
Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql st...
How to solve "TableAdapter can't see stored procedure returned fields when using temp table" problem