Remove Duplicate Values from a table

,

This script will help in removing the duplicate values from a table.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create  procedure Usp_deleteDupValues as

DECLARE csr1 CURSOR FOR
SELECT Col1, Col2, COUNT(*)
FROM mytesttable -- Give your tablename having duplicate rows
GROUP BY Col1, Col2
HAVING COUNT(*) > 1 --select values having at least one duplicate only
FOR READ ONLY 

DECLARE @Col1 INT
DECLARE @Col2 INT
DECLARE @count INT
OPEN csr1
FETCH NEXT FROM csr1 INTO @Col1, @Col2, @count

WHILE @@FETCH_STATUS <> -1
BEGIN
IF @@FETCH_STATUS <> -2
BEGIN
SET @count = @count - 1 --delete all duplicates except one (1)
SET ROWCOUNT @count
DELETE FROM mytesttable -- Give same tablename given above for removing duplicate rows

WHERE Col1 = @Col1 AND Col2 = @Col2
END --IF
FETCH NEXT FROM csr1 INTO @Col1, @Col2, @count
END --WHILE
CLOSE csr1
DEALLOCATE csr1
SET ROWCOUNT 0 --restore default of all rows








GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate