---
title: "Remove Delete Duplicate Records or Rows - SQL Server"  
description: "Hey Guys!! In this article, I will be explaining you, how to delete the duplicate records from the database table."  
author: "mohan kumar"  
published: 2012-01-07  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/854/remove-delete-duplicate-records-or-rows-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Remove Delete Duplicate Records or Rows - SQL Server

Hey Guys!! In this article, I will be explaining you, how to delete the [duplicate records](https://answers.mindstick.com/qa/116620/how-do-you-find-duplicate-records-in-a-sql-server-table) from the [database table](https://www.mindstick.com/forum/159366/how-to-test-regular-expressions-in-sql-without-using-database-table).\

![Remove Delete Duplicate Records or Rows - SQL Server](https://www.mindstick.com/mindstickarticle/0f77aecb-c8f0-4544-be8b-1ceed55e1d5f/images/172eeec3-3155-4317-a642-de5e39d05a45.png)

Remove or Delete duplicate records or rows from ms SQL server database table.\
In this post i am going to describe different methods of deleting duplicate records or rows from sql server database table.\
I am using Employees table with FirstName and Department columns.

\
![Remove Delete Duplicate Records or Rows - SQL Server](https://www.mindstick.com/mindstickarticle/0f77aecb-c8f0-4544-be8b-1ceed55e1d5f/images/b4fd2178-c713-4474-90ab-360688543b53.png)

\

##### First Method.

##### Delete duplicate records/rows by creating identity column.

\
Duplicate records in table looks like shown in first image.\
\
First of all we need to create a [identity column](https://www.mindstick.com/blog/329/add-identity-column-in-datatable-in-c-sharp) in our table by using code mentioned below.\
\
And table will look like image on the left. \
\
[ALTER TABLE](https://www.mindstick.com/forum/7/alter-table) dbo.[Employees](https://yourviews.mindstick.com/view/81150/corona-lockdown-let-s-worry-for-employees) ADD ID INT IDENTITY(1,1)

##### Now write this query to delete duplicate rows.

```
DELETE FROM dbo.EmployeesWHERE ID NOT IN (SELECT MIN(ID)FROM dbo.Employees GROUP BY FirstName,Department)
```

\
This should remove all duplicate records from table.\

##### Second Method:-

##### Remove duplicate rows/Records using temporary table.

\
Use below mentioned code to delete duplicates by moving them to [temporary table](https://www.mindstick.com/forum/158395/explain-the-difference-between-a-temporary-table-and-a-regular-table-in-sqlite) using DISTINCT.

```
SELECT DISTINCT * INTO TempTable FROM dbo.EmployeesGROUP BY FirstName,DepartmentHAVING COUNT(FirstName) > 1 DELETE dbo.Employees WHERE FirstNameIN (SELECT FirstName FROM TempTable) INSERT dbo.Employees SELECT * FROM TempTableDROP TABLE TempTable
```

![Remove Delete Duplicate Records or Rows - SQL Server](https://www.mindstick.com/mindstickarticle/0f77aecb-c8f0-4544-be8b-1ceed55e1d5f/images/30e847e2-128f-4e88-91f2-d1a9ada7879b.png)

And result will be as shown.\
\
Just post back a reply if this article was useful for you. Thanks for reading my article. Happy Coding!! Explore more and more as much as possible.

\

---

Original Source: https://www.mindstick.com/articles/854/remove-delete-duplicate-records-or-rows-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.