How to delete duplicate records from a table in sql server?

There are lot of ways to delete duplicate records from table in sql server.

Here in this article , I will tell you 2 methods through which you can delete duplicate records from the table.

Example:

1st create a sample table.

CREATE TABLE STUDENT
(
                STUDENT_ID                       INT,
                FIRST_NAME                       VARCHAR(25),
                LAST_NAME                        VARCHAR(25),
                FATHE_NAME                     VARCHAR(25),
                ADDR                                    VARCHAR(400),
                PHONE                                  BIGINT
)

 Insert data into STUDENT table :


INSERT INTO STUDENT VALUES(1,'JOE','CAMERO','AARON','19 Quaker Ridge Rd.','6023458976')
INSERT INTO STUDENT VALUES(1,'JOE','CAMERO','AARON','19 Quaker Ridge Rd.','6023458976')
INSERT INTO STUDENT VALUES(2,'Kathleen','Kelly','Devin','7100 Athens Place Washington','4083458976')
INSERT INTO STUDENT VALUES(2,'Kathleen','Kelly','Devin','7100 Athens Place Washington','4083458976')
INSERT INTO STUDENT VALUES(3,'Jeff','Willams','Jean','8400 London Place Washington','4083128400')
INSERT INTO STUDENT VALUES(3,'Jeff','Willams','Jean','8400 London Place Washington','4083128400')
INSERT INTO STUDENT VALUES(4,'Dennis','Charle','Demarcus','1000 Coney Island Ave. Brooklyn NY 11230','3022151541')
INSERT INTO STUDENT VALUES(4,'Dennis','Charle','Demarcus','1000 Coney Island Ave. Brooklyn NY 11230','3022151541')
INSERT INTO STUDENT VALUES(5,'Adan','Fred','Abraham','5520 Quebec Place ; Washington','3022151541')
INSERT INTO STUDENT VALUES(5,'Adan','Fred','Abraham','5520 Quebec Place ; Washington','3022151541')
INSERT INTO STUDENT VALUES(6,'Omarion','Balla','Oliver','3290 Hermosillo Place ;Washington','3022178541')
INSERT INTO STUDENT VALUES(7,'Omari','Richard','Deshaun','1500 Vance Ave','3022178231')
INSERT INTO STUDENT VALUES(7,'Omari','Richard','Deshaun','1500 Vance Ave','3022178231')
INSERT INTO STUDENT VALUES(8,'Mill','Mike','Carol','4150 Sydney Place ; Washington','3022178231')
INSERT INTO STUDENT VALUES(9,'Joseph','Fredric','George','915 E 7th St. Apt 6L;Brooklyn NY 11230','3029875634')
INSERT INTO STUDENT VALUES(10,'Joaquin','Norman','Deonte','7100 Athens Place;Washington','4158907654')
INSERT INTO STUDENT VALUES(11,'Denise','Carter ','Williams','6170 Peshwar Place','4123567654')
INSERT INTO STUDENT VALUES(11,'Denise','Carter ','Williams','6170 Peshwar Place','4123567654')



Method 1: Delete duplicate records from a table using Temp Table.

By using the temporary table we can delete the duplicate records from the table.

1.  select distinct records from the main table into a temporary table.
2. Delete complete data from main table.
3. Insert the records from temp table into main table.

SELECT DISTINCT * INTO #TEMP
FROM STUDENT

DELETE FROM STUDENT

INSERT INTO STUDENT SELECT * FROM #TEMP


Method 2: Delete duplicate records from a table using CTE (Common Table Expression):

CTE(Common Table Expression) can be used to delete the duplicate records from the table.

Here is the sample query to delete duplicate records from STUDENT table:


WITH STUDENT_CTE AS
(
      SELECT ROW_NUMBER() OVER(PARTITION BY STUDENT_ID
      ORDER BY FIRST_NAME, LAST_NAME,
                  FATHE_NAME, ADDR,PHONE)
      AS ROW_NUM
      FROM STUDENT  
)
DELETE FROM STUDENT_CTE WHERE ROW_NUM > 1






This entry was posted in . Bookmark the permalink.

Leave a reply