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).

Answer 1

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