• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

script find all duplicates rows on all tables


I search a script for find all duplicates rows on all tables and insert the duplicates_rows on a temp table.

1 Solution
Lawrence BarnesCommented:
It seems like you are asking to put the duplicate rows into "one" temp table.  The issue is that the columns of the tables with duplicate rows may be different and putting them into "one" temp table isn't possible.  It's also complicated because the data types will be different.

If I had to do what I think you would want to do...I would create a union query that combines the key fields of the tables I want to review, changes them all to varchar, identifies the duplicates using a having clause and then write that to a temp table.  Below is an example.

INTO #tmpDuplicates
FROM (	SELECT 'SampleTable' as TableName
            , CAST(t.AgencyID as VARCHAR) as Column1
            , CAST(t.ChainCode as VARCHAR) as Column2
            , CAST(t.StoreNumber as VARCHAR) as Column3
            , CAST(t.PartNumber as VARCHAR) as Column4
            , CAST(t.Quantity as VARCHAR) as Column5
            , NULL as Column6 --Each query in a union has to have the same number of columns so the NULL was added here to match the number of columns below.
            , COUNT(1) as NbrRows
        FROM #dbo.SampleTable t
        GROUP BY t.AgencyID
            , t.ChainCode
            , t.StoreNumber
            , t.PartNumber
            , t.Quantity
        HAVING COUNT(1) > 1
        UNION ALL
        SELECT 'OtherSampleTable' as TableName
            , CAST(ot.AgencyID as VARCHAR) as Column1
            , CAST(ot.ChainCode as VARCHAR) as Column2
            , CAST(ot.StoreNumber as VARCHAR) as Column3
            , CAST(ot.Address1 as VARCHAR) as Column4
            , CAST(ot.City as VARCHAR) as Column5
            , CAST(ot.State as VARCHAR) as Column6
            , COUNT(1) as NbrRows
        FROM dbo.OtherSampleTable ot
        GROUP BY ot.AgencyID, ot.ChainCode, ot.StoreNumber, ot.Address1, ot.city, ot.state, ot.state, ot.State
        HAVING COUNT(1) > 1 
    ) x

Open in new window

SharathData EngineerCommented:
Provide more details like sample data, expected result.
1.... what do you mean by duplicate data...

you need to explain your scenario in more detail....

are you talking about duplicate data within tables...
or data duplicated across several individual tables...

and at what level is the data to be considered a duplicate ...
  every column the same...
  most columns except some audit/timestamp columns...
  across historic periods within the data across several rows on the table...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now