SQL Script - Remove duplicates
v. 7.0 +
Written by
Last published at: April 14th, 2023Script 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)