Sunday, 20 March 2016

Delete the duplicate records from the Table SQL SERVER

In this post we are going to see how to delete the duplicate records from the table using SQL SERVER. For this we are going to create a employee table and insert a duplicate records.

CREATE TABLE EMPLOYEE(
     ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
     FIRSTNAME VARCHAR(50),
     LASTNAME VARCHAR(50),
     DESIGNATION VARCHAR(50),
     PHONE INT,
     DEPARTMENTID VARCHAR(30)
)





/************** Insertion of the records ************/
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAJESH','G','ARCHITECT',1231232,'D1'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'SURESH','G','DEVELOPER',342232,'D1'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAMU','F','QA',124232,'D2'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'SHINY','E','HR',1231232,'D3'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'KRISHNA','G','QA',2343,'D2'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAMU','Y','QA',124232,'D3'

INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'SURESH','G','DEVELOPER',342232,'D1'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAMU','F','QA',124232,'D2'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'SHINY','E','HR',1231232,'D3'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'KRISHNA','G','QA',2343,'D2'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAMU','Y','QA',124232,'D3'

INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAJESH','G','ARCHITECT',1231232,'D1'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'SURESH','G','DEVELOPER',342232,'D1'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAMU','F','QA',124232,'D2'
INSERT INTO EMPLOYEE (FIRSTNAME,LASTNAME,DESIGNATION,PHONE,DEPARTMENTID)
SELECT 'RAMU','Y','QA',124232,'D2'



From the above records inserted we want to delete the duplicate records, other than identity column everything needs to be unique, so we are going  to partition the table in row_number over the columns other an identityor primary key

      SELECT 'ACTUAL RECORDS'
      SELECT * FROM EMPLOYEE


     ;WITH EMP AS
     (
          SELECT *, ROW_NUMBER() 
          OVER(PARTITION BY FIRSTNAME,
                            DESIGNATION,
                            PHONE,
                            LASTNAME,
                            DEPARTMENTID 
                   ORDER BY DEPARTMENTID) AS ROWNUMBER 
           FROM EMPLOYEE
      )

    DELETE FROM EMP WHERE ROWNUMBER > 1

    SELECT 'AFTER DELETE DUPLICATE RECORDS'
    SELECT * FROM EMPLOYEE










From this post you can learn how to delete the duplicate records from the Table



No comments:

Post a Comment