TSQL: Remove duplicates

v. 7.0 +

Written by Ines

Last published at: April 7th, 2022

Script 1 - checking duplicates

 

SELECT f.* INTO #DuplicatesToDelete

FROM DeviceFulls f

JOIN DeviceSlims s ON f.DevicePk = s.DevicePk

WHERE f.DevicePk NOT IN

       (SELECT MAX(DevicePk)

       FROM DeviceFulls f

       GROUP BY f.DeviceName)

 

SELECT * FROM #DuplicatesToDelete



Script 2 - deleting duplicates


DELETE FROM DeviceFulls

WHERE DevicePk IN (SELECT DevicePk FROM #DuplicatesToDelete)

 

DELETE FROM DeviceSlims

WHERE DevicePk IN (SELECT DevicePk FROM #DuplicatesToDelete)