Delete Duplicates

,

Delete Duplicates with a cursor

/* Setup test */

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[TableWithDuplicates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [TableWithDuplicates]
GO

CREATE TABLE TableWithDuplicates (
KeyColumn VARCHAR(100) PRIMARY KEY NOT NULL,
Column1 VARCHAR(100),
Column2 VARCHAR(100)
)

INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) 
VALUES ('A','Duplicate2','Duplicate3')
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('B','Duplicate2','Duplicate3')
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('C','Duplicate2','Duplicate3')

INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2)
VALUES ('D','DuplicateY','DuplicateZ')
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('E','DuplicateY','DuplicateZ')
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('F','DuplicateY','DuplicateZ')

-- Before
SELECT * FROM TableWithDuplicates 

/* Run test */
DECLARE @KeyColumn VARCHAR(100)
DECLARE @Column1 VARCHAR(100)
DECLARE @Column2 VARCHAR(100)
DECLARE @PreviousColumn1 VARCHAR(100)
DECLARE @PreviousColumn2 VARCHAR(100) 

DECLARE Duplicate_Cursor CURSOR
FOR
SELECT 
	KeyColumn, 
	COALESCE(Column1,'') AS Column1, 
	COALESCE(Column2,'') AS Column2
FROM TableWithDuplicates
ORDER BY 
	KeyColumn,
	Column1,
	Column2

SET @PreviousColumn1 = ''
SET @PreviousColumn2 = ''

OPEN Duplicate_Cursor
FETCH NEXT 
FROM Duplicate_Cursor
INTO @KeyColumn, @Column1 , @Column2

WHILE @@FETCH_STATUS = 0
BEGIN

	IF @Column1 = @PreviousColumn1
	   AND
	   @Column2 = @PreviousColumn2
		DELETE FROM TableWithDuplicates 
		WHERE CURRENT OF Duplicate_Cursor

	SET @PreviousColumn1 = @Column1
	SET @PreviousColumn2 = @Column2

	FETCH NEXT 
	FROM Duplicate_Cursor
	INTO @KeyColumn, @Column1 , @Column2
END

CLOSE Duplicate_Cursor
DEALLOCATE Duplicate_Cursor

-- After
SELECT * FROM TableWithDuplicates

Rate

Share

Share

Rate