How to delete duplicate rows in SQL server

Hi Alok,

do simple steps......

step1.select Distinct a.* into temp1 from tablename a
step2. delete from tablename
step3. insert temp1 to tablename like insert into tablename(column1,column2....)  select * from temp1


Thanks,
Pravin.

 

Hi Alok,

Please Check with below Sql to get duplicate rows according your question.

create
table #DuplicateTable (id int,name varchar(20));insert
into #DuplicateTable values(1,'a') ;insert
into #DuplicateTable values(1,'a') ;insert
into #DuplicateTable values(2,'b') ;insert
into #DuplicateTable values(3,'c') ;insert
into #DuplicateTable values(4,'d') ;insert
into #DuplicateTable values(4,'d') ;insert
into #DuplicateTable values(1,'e') ;select
* from #DuplicateTable----------------------------------------------
SELECT
*INTO #duplicatedata FROM #DuplicateTableGROUP
BY ID,nameHAVING
COUNT(*) > 1select
* from #duplicatedata-------------------------
-- now use below sql to delete duplicate rows
DELETE
FROM #DuplicateTableFROM
#DuplicateTableINNER
JOIN #duplicatedataON
#DuplicateTable.ID = #duplicatedata.IDAND
#DuplicateTable.Name = #duplicatedata.Name------select again main table and see duplicates deleted r not
select * from #DuplicateTable
select * from #Duplicatedata







Thanks.

Hi Alok ,

As you mentioned without primary key in table, you can try below code,

select * from temp_t ;
select name, COUNT(name) as c, max(id) as m into #temp_new from temp_t group by name having COUNT(name) >1;
select * from temp_t where id not in (select m from #temp_new ) order by name;

Hope this will help you .

Thanks

Hi Alok Ji.....

Please visit this link for your problem

http://stackoverflow.com/questions/985384/delete-duplicate-records-from-a-sql-table-without-a-primary-key


http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/




Thanks..