Solved

script find all duplicates rows on all tables

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

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to PARSE a text field that is delimited by '~' character? 3 61
Set the max value for a column 7 38
Help with simplifying SQL 6 53
SQL Error - Query 6 25
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 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