Delete Duplicate Rows from a table in SQL SERVER 2012

Delete Duplicate Rows from a table in SQL SERVER 2012

1) Create a table called Person 

CREATE TABLE [dbo].[Person] (
    [Name] VARCHAR (20) NULL,
    [idd]  INT          IDENTITY (1, 1) NOT NULL
);

2) Insert data to Person Table

insert into person(name) values('Paul'),('Jhonson'),('jenny'),('mathew')

3) Select * from Person

Name    idd
Paul    1
Jhonson    2
jenny    3
mathew    4

4) Repeat the Name column with the following command

insert into person(name) select name from person

5) select * from Person

Name    idd
Paul    1
Jhonson    2
jenny    3
mathew    4
Paul    9
Jhonson    10
jenny    11
mathew    12

Here step 4 records are repeated with same name but different IDD column values.

 

6) Display repeated rows 

Displays repeated name records here so that you can delete those records.

 

select * from persond p where idd in (select max(idd) from persond

group by name)

Name    idd
Paul    9
Jhonson    10
jenny    11
mathew    12
 

the above rows has repeated  names .(this step is for testing purpose only)

 

7) Delete Duplicate Name column values

delete  from person  where idd in (select max(idd) from person
group by name)

8) select * from person

Name    idd
Paul    1
Jhonson    2
jenny    3
mathew    4

Tags: Delete Duplicate Rows from a table in SQL SERVER 2012,Delete duplicate columns from a table, Create table in sql server, create column with identity value,remove duplicate columns, How to remove duplicate records from a table in sql server 2012.