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
