Delete Duplicate Records from Table

Question

Hi All,

I have a requirement from Client, I need to delete duplicate records from one table, Before delete we need to insert records into another table for auditing.

what is the best approach to achieve this.

Thanks in Advance.

Answer 1

Hi BHVS:

a possible solution

Create table dbo.demo (
id int, 
val varchar(100)
);
Go
Drop table if exists dbo.DemoLog; -- SQL Server 2016 or +
Go
Insert into dbo.demo (id, val)
values
(1,'a'),
(1,'a'),
(2,'b'),
(3,'b'),
(1,'a'),
(4,'c'),
(4,'c'),
(1,'a'),
(5,'d'),
(6,'e');
go

identify duplicates using the function: Row_number

with R as (
	Select *, ROW_NUMBER() over (partition by id order by (Select null)) as row --(-- here order)
	From dbo.demo
)
Select *
from R

-- Create and Insert into demoLog

Drop table if exists dbo.demoLog;

with R as (
	Select *, ROW_NUMBER() over (partition by id order by (Select null)) as row --(-- here order)
	From dbo.demo
)
Select R.id, R.val into demolog 
from R
Where row >1;

--  Delete

with R as (
	Select *, ROW_NUMBER() over (partition by id order by (Select null)) as row --(-- here order)
	From dbo.demo
)
Delete from R
Where row >1;

go

Answer 2

Adding on to Javi's example, you could also create the audit table beforehand and then specify an OUTPUT clause on the delete to perform the insert/delete in one go:

CREATE TABLE dbo.demo_log (
	id int, 
	val varchar(100)
);
WITH R AS (
	SELECT
		  id
		, val
		, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS row_num
	FROM dbo.demo
)
DELETE FROM R
OUTPUT deleted.id, deleted.val INTO dbo.demo_log
WHERE row_num > 1;

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 3

Hi All,

I have a requirement from Client, I need to delete duplicate records from one table, Before delete we need to insert records into another table for auditing.

what is the best approach to achieve this.

Thanks in Advance.

Hi,

1. Create temp table having only distinct records of current table.

2. Truncate current table (before doing this, you can also have a backup of original table).

3. Select all records of temp table in 1), into original table.

---

Many Thanks & Best Regards, Jackson Chen

Answer 4

Hi

 select distinct * into #tmp From yourtable
       delete from yourtable

Thanks and regards

Answer 5

Hi BHVS,

Insert records into another table:

select * into newtable from yourtable
select * from newtable 

Delete duplicate records:

select distinct * into newtable2 from yourtable 
drop table yourtable

select * from newtable2

If only a field is repeated instead of a row, please refer to Dan's answer.

Best Regards
Echo

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 6

Hi BHVS,

Has your problem been solved? If it is solved, please mark the point that you 
think is correct as an answer. This can help others who encounter similar problems.

Best Regards 
Echo 

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.