SQL Server - Move duplicate records into a different table

Question

Employee_table

Id	FirstName	LastName	Division
1	John	Smith	A
2	Emily	Jones	B
3	John	Smith	B
4	John	Snow	A
5	John	Smith	C

I want to move all duplicate names from Employee_table to a table named Dupes. Dupes has the all the same columns as Employee_table (assume both tables have more columns after Division).

In the table above, there are 3 dupes of John Smith, so all three need to be moved to Dupes (within a Transaction). How do I achieve that?

EDIT: Can we keep the query as simple as possible? I am new to SQL (this is not homework).

SQL Server 2008 or 2012? The former is *completely* unsupported (has been for almost 3 years) and the latter is very close to the end of it's life cycle. Most certainly, regardless of which, you should be well into getting your upgrade path completed. — Thom A, Apr 29 '22 at 12:40

D-Shih · Answer 1 · 2022-04-29T14:19:21.357

2

Firstly, we might create a query to find which rows duplicate as question logic from Employee_table table.

So we can use COUNT window function which is greater than one to represent duplicate as this sqlfiddle

;WITH CTE AS (
    SELECT *,COUNT(*) OVER(PARTITION BY FirstName,LastName) cnt
    FROM Employee_table 
)
SELECT * 
FROM CTE
WHERE cnt > 1

Then we can use DELETE clause with CTE and filter cnt > 1 which means duplicate rows as this sqlfiddle

;WITH CTE AS (
    SELECT *,COUNT(*) OVER(PARTITION BY FirstName,LastName) cnt
    FROM Employee_table 
)
DELETE CTE
WHERE cnt > 1

Final, we can combine before stop and use DELETE with OUTPUT .... INTO to Dupes table, we can use deleted to get deleted row from OUTPUT

;WITH CTE AS (
    SELECT *,COUNT(*) OVER(PARTITION BY FirstName,LastName) cnt
    FROM Employee_table 
)
DELETE FROM CTE 
OUTPUT deleted.id,
       deleted.FirstName,
       deleted.LastName,
       deleted.Division
INTO Dupes (id,FirstName,LastName,Division)
WHERE cnt > 1

sqlfiddle

edited Apr 29 '22 at 14:19

answered Apr 29 '22 at 12:48

D-Shih

44,943
6
31
51

Is there a way to write this query using less complex syntax? I am new to SQL. – bigShaq Apr 29 '22 at 14:01
Hi @bigShaq Because you are doing `INSERT` to table from another `DELETE` table in a transaction which might be a little complex, I would add more explain for you as possible, one second – D-Shih Apr 29 '22 at 14:06
Please note, Dupes has the same columns as Employee_table. – bigShaq Apr 29 '22 at 14:09
Hi @bigShaq I had edited my answer and tried to explain more detail as possible (split all steps) hope that help – D-Shih Apr 29 '22 at 14:20

SQL Server - Move duplicate records into a different table

1 Answers1