How can you delete duplicate records in sql server?

Atul Sinha
Jun 04, 2007

Hi Dear, i'll give you only three steps that will remove the duplicate records from your database table..... STEP 1 - Insert your distinct data into a temporary table select distinct* into temp from TableName STEP 2 - Delete From TableName STEP 3 - Insert TableName Select * From temp Write three step and remove duplicate values from ur tables....

Atul Sinha
Jun 04, 2007

Hi Dear, i'll give you only three steps that will remove the duplicate records from your database table..... STEP 1 - Insert your distinct data into a temporary table

Prasanna Sri
Mar 29, 2007

First you have to create the table names as temp1. The syntax is given below. Create table temp1(eno int,varchar(20)) Then insert the duplicate values into that table (temp1). Then create the trigger given below. create trigger deleteduplicate on temp1 after insert as select eno,ename,count1=count(*) into temp2 from temp1 group by eno,ename having count(*)>1 select * from temp2 select distinct temp1.* into temp3 from temp1,temp2 where temp1.eno=temp2.eno and temp1.ename=temp2.ename delete temp1 from temp1,temp2 where temp1.eno=temp2.eno and temp1.ename=temp2.ename insert temp1 select * from temp3 drop table temp2 drop table temp3 When you will be inserting the record, then this trigger get fired and will elimnate the duplicate records.

Prasanna Sri
Mar 29, 2007

First you have to create the table names as temp1. The syntax is given below. Create table temp1(eno int,varchar(20)) Then insert the duplicate values into that table (temp1). Then create the trigger given below. create trigger deleteduplicate on temp1 after insert as select eno,ename,count1=count(*) into temp2 from temp1 group by eno,ename having count(*)>1 select * from temp2 select distinct temp1.* into temp3 from temp1,temp2 where temp1.eno=temp2.eno and temp1.ename=temp2.ename delete temp1 from temp1,temp2 where temp1.eno=temp2.eno and temp1.ename=temp2.ename insert temp1 select * from temp3 drop table temp2 drop table temp3 When you will be inserting the record, then this trigger get fired and will elimnate the duplicate records.

Mar 27, 2007

first movethe records of original table into temp table using distinct condition here duplicate records doesnot move to temp table

then delete the original table

and finally move to original table from temp table.

select distinct * from emp into #emp1

delete table emp

select into emp from # emp1

select * from emp

it doesnt show duplicate records