Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

script find all duplicates rows on all tables

Posted on 2013-11-20
3
Medium Priority
?
276 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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

636 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