Get duplicate records

Last Post 27 Jun 2006 02:35 PM by dianagele. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
michael.mondragon
New Member
New Member

--
26 Feb 2006 06:22 PM
hi! i jst want 2 ask 4 some help in regards to duplicate records. i know about select distinct but it just filters and displays distinct records right? but for me i need to have all the duplicate records and transfer it to other database/ table. is there any sql commands that i can use to do it? hope u cud help me with this! thankx!
whynot
New Member
New Member

--
28 Feb 2006 06:30 AM
Maybe you want this:


SELECT * into otherdatabase..othertable FROM mytable

SQLUSA
New Member
New Member

--
03 Mar 2006 08:25 PM
To dedup it you need to uniquely identify them:


select SuperID=identity(int,1,1), * into #deduperTableX from TableX


You only keep the min(SuperID) for each repeating group, delete the rest.


Kalman Toth, Database Architect
http://www.sqlusa.com/orderdoubleheader/
The Best SQL Server 2005 Training in the World

dianagele
New Member
New Member

--
27 Jun 2006 02:35 PM
This came from my recipe box:

SELECT DuplicateName
FROM dbo.TableA
WHERE (DuplicateName IN
(SELECT DuplicateName
FROM TableA
GROUP BY DuplicateName
HAVING COUNT(*) > 1))
SwePeso
New Member
New Member

--
16 Apr 2007 01:32 AM
quote:

Originally posted by: SQLUSA
select SuperID=identity(int,1,1), * into #deduperTableX from TableX
You only keep the min(SuperID) for each repeating group, delete the rest.

If you want to release the power of SQL Server 2005, try this

DELETE t1
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2 DESC) AS RecID
FROM Table1
) AS t1
WHERE RecID > 1


You are not authorized to post a reply.

Acceptable Use Policy