Post reply

How to Select duplicated records in a table into new table OR Delete all unique records in a file!

  • October 13, 2007 at 9:37 pm

    #182873

    Hello all,

    I have a big data table that contains duplicated records. I would like to extract all duplicated SSN records with other datas into a new table. The data table as following:

    Tablename: CustomerRecords

    SSN | FullName | Purchase Date .....

    1112223333 | John Smith | 02/12/2007

    5556667777 | Allen Lee | 12/03/2006

    1112223333 | John Smith | 05/06/2005

    9990001111 | Julie Ford | 12/09/2003

    1112223333 | John Smith | 08/02/2004

    .

    .

    .

    I knew my table having duplicated records by doing the bellowed query:

    SELECT SSN, COUNT (SSN) AS NumOccurrences

    FROM CustomerRecords

    GROUP BY SSN

    HAVING (COUNT(SSN) > 1)

    Any help either show me the TSQL for Select duplicated SSN records into new table or Delete the unique SSN records of CustomerRecords is much appreciated. Thanks in advance.

  • October 13, 2007 at 10:17 pm

    #741827

    you had almost everything you need...you simply want to sub select:

    SELECT * FROM CustomerRecords

    WHERE SSN IN (SELECT SSN

    FROM CustomerRecords

    GROUP BY SSN

    HAVING (COUNT(SSN) > 1)

    ) X

    ORDER BY SSN

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 13, 2007 at 11:11 pm

    #741831

    Based on your TSQL, I used the following query:

    SELECT * FROM CustomerRecords

    WHERE SSN IN

    (SELECT SSN

    FROM CustomerRecords

    GROUP BY SSN

    HAVING (COUNT(SSN) > 1))

    ORDER BY SSN

    It worked perfectly. Is that your typo in X ...? You saved my time to solve this matter. Anyway, thanks for your genius.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply