Solved

script find all duplicates rows on all tables

Posted on 2013-11-20
3
271 Views
Last Modified: 2013-12-05
Hello,

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

Thanks
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 500 total points
ID: 39663139
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.

SELECT x.*
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

0
 
LVL 41

Expert Comment

by:Sharath
ID: 39668245
Provide more details like sample data, expected result.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39668665
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...
0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question