0 Replies - 1623 Views - Last Post: 20 May 2008 - 04:15 PM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

MSSQL snippet for removing duplicate records

Posted 20 May 2008 - 04:15 PM

Description: Items that need to be replaced in this script YourTable : Your table name Column1, Column2 (etc) : Your column namesThis is a snippet I use for "de-duping" a table (removing all duplicate entries". This process doesn't use temporary tables or cursors, adding to it's efficiency
DECLARE @Count INT
SET @Count = 0
SELECT @Count = COUNT(*)
	FROM YourTable
	GROUP BY Column1
	HAVING COUNT(*) > 1

WHILE @Count > 0
BEGIN
	SET ROWCOUNT 1

	DELETE YourTable
	FROM YourTable t1
	JOIN (SELECT Column1, Column2
		FROM YourTable
		GROUP BY Column1, Column2
		HAVING COUNT(*) > 1
	      ) t2
	ON  t1.Column1 = t2.Column1
	AND t1.Column2 = t2.Column2
	
	SET ROWCOUNT 0

	SELECT @Count = COUNT(*)
	FROM    (SELECT Column1, Column2
		 FROM YourTable
	 	 GROUP BY Column1, Column2
		 HAVING COUNT(*) > 1
		) total
END




Is This A Good Question/Topic? 0
  • +

Page 1 of 1