Handy: Remove duplicated Records in SQL

Basic? maybe.. but a handy script in T-SQL to remove duplicated records.

(I’m just using an example "jobs" table.. change this to yours, otherwise the other tables are simply
temp tables for the purpose of storing duplications etc)

/* First get a count of duplicates */
select jobno, count(jobno) as numdups from jobs
group by jobno having (count(jobno) >1)

/* Get the duplicated keys */
select jobno, count(jobno) as numdups
into holdkey
from jobs
group by jobno
having count(jobno) > 1

/* Get just one each of the duplicated records */
select distinct jobs.*
into holddups
from jobs, holdkey
where jobs.jobno = holdkey.jobno

/* Now delete the orginal duplicated table */
delete jobs
from jobs, holdkey
where jobs.jobno = holdkey.jobno

/* Copy back the distinct single records without
   any duplications */
insert jobs select * from holddups

You can of course at each stage query the tables to check what is going on.