FIND DUPLICATE ROWS IN A TABLE IN SQL SERVER 2005
CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)
SELECT * FROM EMPLOYEE
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 80000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 40000 )
INSERT INTO EMPLOYEE VALUES ( ‘Rahul’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Gauri’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Gauri’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Rahul’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Deepa’ , 100000 )
–Query to find out the rows with duplicate names in the above table
–Ouery will give u the name
SELECT EmpName FROM EMPLOYEE GROUP BY EmpName HAVING COUNT(EmpName) > 1
–Query if you all the details
SELECT id , EmpName , Salary FROM Employee WHERE EmpName IN
(SELECT EmpName FROM EMPLOYEE GROUP BY EmpName HAVING COUNT(EmpName) > 1)
Pawan Kumar
Pawankkmr@hotmail.com