creating a table with duplicates-

[eyhandle]

I have a table called table1. I would like to create a table called table2 that will contain all the duplicates in table1 based on fields:same-type, ssn or id, date and code.


my atempt below doesn't work. if you have any other ideas, please let me know.

select *
insert into table2
from table1 r1
where type='f'
and type in
(Select type
from table1
where type='f'
group by type, social_security, id, date, code having count(*)>1
and ((social_security=r1.social_security) or (id=r1.id)) and
(date=r1.date) and (code=r1.code))

 

[lyudmila]

1)first step to find duplicate records by these field and create table based on them

select ssn, id,date,code, reccnt=count(*)
into temp
from Table1
where type='A'
group by ssn, id,date,code
having count(*)>1

2) create table from dups records in table 1
select *
into Table 2
from Table 1 where exists
(select * from temp b where
a.ssn=b.ssn
and a.id=b.id
and a.date=b.date
and a.code=b.code)