Delete Duplicate Records from Table

This code is copied from the link mentioned below

http://www.codeproject.com/Articles/25622/Sql-Queries-most-asked-in-job-interviews

Let the table employee_test1 contain some duplicate data like:-

CREATE TABLE Employee_Test1

(

Emp_ID INT,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);

INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);

INSERT INTO Employee_Test1 VALUES (3,'John',1100);

INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);

INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);

INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

Step 1: Create a temporary table from the main table as:-

e

select top 0* into employee_test1_temp from employee_test1

Step2 : Insert the result of the GROUP BY query into the temporary table as:-

insert into employee_test1_temp

select Emp_ID,Emp_name,Emp_Sal

from employee_test1

group by Emp_ID,Emp_name,Emp_Sal

Step3: Truncate the original table as:-

truncate table employee_test1

Step4: Fill the original table with the rows of the temporary table as:-

insert into employee_test1

select * from employee_test1_temp

Now, the duplicate rows from the main table have been removed.

e

select * from employee_test1

gives the result as:-

Emp_ID   Emp_name   Emp_Sal

1        Anees       1000

2        Rick        1200

3        John        1100

4        Stephen     1300

5        Maria       1400

6        Tim         1150

(b) Without using a temporary table

;with T as

(

         select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank

         from employee_test1

)

delete

from T

where rank > 1

The result is as:-

Emp_ID   Emp_name   Emp_Sal

1        Anees       1000

2        Rick        1200

3        John        1100

4        Stephen     1300

5        Maria       1400

6        Tim         1150