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.
From this post you can learn how to delete the duplicate records from the Table
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