Technical Article

Remove Duplicate Records

,

If you want to delete duplicate records in a large table with out creating temp tables.

This script is enhanced version of http://www.sqlservercentral.com/scripts/T-SQL/62352/

 

--Write by Syed Iqbal
-- @ www.sqldba.org
-- Modified version of http://www.sqlservercentral.com/scripts/T-SQL/62352/
-- Delete records with out temporary tables.

CREATE TABLE #phonebook (
[phonenumber] [varchar] (30),
[firstname] [varchar] (30),
[lastname] [varchar] (30),
[company] [varchar] (100)
)

INSERT #phonebook SELECT '902','syed','iqbal','sm soft'
INSERT #phonebook SELECT '905','john','ksjl89','company llc'
INSERT #phonebook SELECT '909','joe','average','united'
-- Duplicate insert 1
INSERT #phonebook SELECT '902','syed','iqbal','sm soft'
INSERT #phonebook SELECT '905','john','ksjl89','company llc'
INSERT #phonebook SELECT '909','joe','average','united'
-- Duplicate insert 2
INSERT #phonebook SELECT '902','syed','iqbal','sm soft'
INSERT #phonebook SELECT '905','john','ksjl89','company llc'
INSERT #phonebook SELECT '909','joe','average','united'

--Show Duplicate Phonenumbers in Phonebook
SELECT phonenumber, COUNT(*) FROM #phonebook
GROUP BY phonenumber HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

SET ROWCOUNT 1
SELECT @@rowcount
WHILE @@rowcount > 0
DELETE pb FROM #phonebook as pb
INNER JOIN
(SELECT phonenumber
FROM #phonebook
GROUP BY phonenumber HAVING count(*) > 1)
AS c ON c.phonenumber = pb.phonenumber
SET ROWCOUNT 0

SELECT * FROM #phonebook

DROP TABLE #phonebook

Rate

3.78 (9)

You rated this post out of 5. Change rating

Share

Share

Rate

3.78 (9)

You rated this post out of 5. Change rating