Technical Article

Removing Duplicate Records

,

Sometimes it is necessary to remove duplicate records from a table.

You can get the idea how to do this with examining this SQL statements. This is an example that removes duplicate phonenumbers from a phonebook table.

 

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

--Delete All Duplicate Records from Phonebook
CREATE TABLE #tmp (
 [phonenumber] [varchar] (30),
 [firstname] [varchar] (30),
 [lastname] [varchar] (30),
 [company] [varchar] (100)
)

--Create a unique index on phonenumber column
--"WITH IGNORE_DUP_KEY" is the key 
CREATE UNIQUE INDEX unqT ON #tmp (phonenumber) WITH IGNORE_DUP_KEY

INSERT INTO #tmp SELECT 
 [phonenumber] ,
 [firstname] ,
 [lastname] ,
 [company]
 FROM phonebook

--Truncate original table
TRUNCATE TABLE phonebook

INSERT INTO phonebook SELECT * FROM phonebook

DROP TABLE #tmp

Rate

2.67 (12)

You rated this post out of 5. Change rating

Share

Share

Rate

2.67 (12)

You rated this post out of 5. Change rating