April 23, 2009 at 2:46 am
Hi all,
I want to select duplicates records from a database into another table. The script below show me how many duplicates for each records are there. What I want is to see those duplicate records by mybe seleting them into another table, how can I do that.
SELECT IDNO, COUNT(IDNO) NR
FROM dbo.filteredcontact
GROUP BY IDNO
HAVING COUNT(IDNO) > 1
Many thanks
P.
April 23, 2009 at 6:58 am
You didn’t specify what duplicate records are. I assume that when you say duplicate records you mean each record that has the same value in IDNO column. The code bellow shows you how to do it:
create table filteredcontact (IDNO int, description varchar(15))
go
--Insert some test data.
insert into filteredcontact (IDNO, description)
select 1, 'one'
union all
select 1, 'one'
union all
select 1, 'Echad'
union all
select 2, 'two'
union all
select 3, 'three'
union all
select 4, 'four'
union all
select 4, 'arba'
union all
select 5, 'five'
go
--Declare a CTE that returns each IDNO that is duplicate
--Later I use the CTE and join it to the real table.
--This will show me only the duplicated records.
;With DuplicatedIDNO as (
SELECT IDNO, COUNT(IDNO) NR
FROM dbo.filteredcontact
GROUP BY IDNO
HAVING COUNT(IDNO) > 1
)
select f.IDNO, f.Description
from DuplicatedIDNO D inner join filteredcontact f on D.IDNO = f.IDNO
go
--cleanup
drop table filteredcontact
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 23, 2009 at 7:50 am
Hi Adi,
You are right, this worked like a charm, thanks.
P.
April 24, 2009 at 1:26 am
It might be also like this (with the Insertion into another table Part...)
Declare @tbl Table (id int, idv varchar(10))
Insert into @tbl
Select 1,'1' Union All
Select 1,'2' Union All
Select 3,'3' Union All
Select 3,'3'
-- To Select
;with wcte as (
Select *,ROW_NUMBER() over (Partition By id Order By id ) RNO
from @tbl
) Select * from wcte where RNO > 1
-- To Insert into another table
;with wcte as (
Select *,ROW_NUMBER() over (Partition By id Order By id ) RNO
from @tbl
) Select * into #t1 from wcte where RNO > 1
Select * from #t1
drop table #t1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply