Interviews - C# Corner

Naveen Sikri
How can you delete duplicate records in sql server?
By Naveen Sikri in SQL Server on Mar 26, 2007
  • Ravi Maheshwari
    Aug, 2015 27

    ;WITH [CTE] as( SELECT ROW_NUMBER() OVER (PARTITION BY [VendorCode] ORDER BY [VendorCode] ) RN FROM #details) delete from cte where RN>1

    • 0
  • Lalit Raghuvanshi
    Feb, 2015 5

    Please refer the article Remove duplicate records/data from Sql Server database table http://www.webcodeexpert.com/2013/11/how-to-remove-duplicate-recordsdata.html

    • 0
  • Atul Sinha
    Jun, 2007 4

    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....

    • 0
  • Atul Sinha
    Jun, 2007 4

    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

    • 0
  • Prasanna Sri
    Mar, 2007 29

    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.

    • 0
  • Prasanna Sri
    Mar, 2007 29

    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.

    • 0
  • Mar, 2007 27

    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

     

    • 0

VSLive! Anaheim

Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS