Delete duplicate records in SQL Server 2000, SqlCE

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 1601
The below code with delete the duplicate records

CREATE TABLE tblTest (ID INT, NAME VARCHAR(20),AGE INT)
INSERT INTO tblTest VALUES(1,'Albela',34),(1,'Albela',34),(2,'Zobora',16),(2,'Zobora',16),(2,'Zobora',16),(3,'Haxxi',23),(4,'Ramu',59)

--create an alternate table to store the duplicate records
CREATE TABLE tblTestDuplicate (ID INT, NAME VARCHAR(20),AGE INT)

-- insert those duplicate records to this new table
INSERT INTO tblTestDuplicate
SELECT *
FROM tblTest
GROUP BY ID,NAME,AGE
HAVING COUNT(ID) > 1

--delete the duplicate records from the original table
DELETE FROM
tblTest
WHERE ID IN (SELECT ID FROM tblTestDuplicate)

--insert all the records into the original table
INSERT INTO tblTest
SELECT *
FROM tblTestDuplicate

--Project the new records
SELECT *
FROM tblTest
ORDER BY ID


--clean up
DROP TABLE tblTestDuplicate
DROP TABLE tblTest

Comments or Responses

Login to post response