Solved

script find all duplicates rows on all tables

Posted on 2013-11-20
3
270 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

733 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