---
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.  Remove or Delete duplicate records"  
author: "mohan kumar"  
published: 2011-11-21  
updated: 2020-03-04  
canonical: https://www.mindstick.com/articles/807/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/a5537f1b-35f5-4994-ab18-4914af75f9d2/images/6f9355f1-8514-42ec-98c2-9330cc763457.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/a5537f1b-35f5-4994-ab18-4914af75f9d2/images/836a2d6e-a07a-469f-bca9-00b972a160b1.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/a5537f1b-35f5-4994-ab18-4914af75f9d2/images/e4e6b35e-6a01-45dd-9d9e-5f4f3fedf363.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/807/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.