Remove Duplicate Rows from a Table in SQL SERVER 2012

Remove Duplicate Rows from a Table in SQL SERVER 2012

1)Create a table called Person

CREATE TABLE [dbo].[Person] (
    [Id]   INT          NULL,
    [Name] VARCHAR (20) NULL
);


This example works if table has all columns  repeated. and it is efficient, if user has small amount of data.


2) Insert records into Person table

insert into person values(1,'Paul'),(2,'Jhonson'),(3,'jenny'),(4,'mathew')

3) select * from Person

Id    Name
1    Paul
2    Jhonson
3    jenny
4    mathew

4) Replicate all these rows again.

insert into person select * from person

5)  select * from Person

Id    Name
1    Paul
2    Jhonson
3    jenny
4    mathew
1    Paul
2    Jhonson
3    jenny
4    mathew

6)   Display Unique Records using Distinct /group by

select distinct id,name  from persond;

Id    Name
1    Paul
2    Jhonson
3    jenny
4    mathew

7)   Copy these unique values to temporary Table

select distinct id,name into #tmp from persond;

9)  Truncate Original Table

truncate table person;

10) Copy back from temporary table to original table.

insert into person select * from #tmp;


11) Table with all unique values.

select * from Person

Id    Name
1    Paul
2    Jhonson
3    jenny
4    mathew



Tags:Remove Duplicate Rows from a Table in SQL SERVER 2012,Remove Duplicate Rows from a Table  all columns has duplicate values , display unique values using distinct,display unique records using distinct,display unique values using group by,display unique records using group by.delete duplicate records from a table in sql server.