How to Identify and Delete Duplicate SQL Server Records

/**********************************************
Example of a complex duplicate data delete script.
**********************************************/
/**********************************************
Set up test environment
**********************************************/
SET NOCOUNT ON
--Create test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO
CREATE TABLE tDupData
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)
--Create test data
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address2','01/15/2003')
INSERT INTO tDupData VALUES (3,'CompanyThree','Address3','01/15/2003')
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
-- Simple Dup Data and complex dup data
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address','01/16/2003')
-- complex dup data
INSERT INTO tDupData VALUES (3,'CompanyThree','Address','01/16/2003')
-- complex dup data
GO
/**********************************************
Finish set up
**********************************************/
/**********************************************
Complex duplicate data
**********************************************/
--Clean table out to include only one row per company
--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)
--Clean out simple duplicate data first
--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress, dtmModified
HAVING COUNT(*) > 1
--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'
--Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON  tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified
--Insert the delete data back
INSERT INTO tDupData
SELECT * FROM #tempduplicatedata
--Check for dup data.
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress,dtmModified
HAVING COUNT(*) > 1
--Clean out temp table
TRUNCATE TABLE #tempduplicatedata
--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata (lngCompanyID,strCompanyName)
SELECT lngCompanyID,strCompanyName FROM tDupData
GROUP BY lngCompanyID,strCompanyName
HAVING COUNT(*) > 1
--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'
--Update temp table to add strAddress and dtmModified
UPDATE #tempduplicatedata
SET strAddress = tDupData.strAddress
,dtmModified = tDupData.dtmModified
FROM #tempduplicatedata
INNER JOIN tDupData
ON #tempduplicatedata.lngCompanyID = tDupData.lngCompanyID
AND #tempduplicatedata.strCompanyName = tDupData.strCompanyName
--Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON  tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified
--Verify original table only has three rows of data
SELECT * FROM tDupData
--Drop temp table
DROP TABLE #tempduplicatedata
--drop test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

This is a little more complicated than the simple duplicate data delete script, but easy to figure out once you see it. A word of caution here, you should investigate any child tables before you delete data from a table in order to prevent creating orphan rows. You can ether delete the data from the child tables first or update them to reflect the identity key value of the data row in the main table you are going to keep. The choice will be determined by your situation and any operating standards you may have.

While having to clean up duplicate data is not something you should have to do every day, the processes you learn from playing with these two scripts should give you a starting point the next time you find duplicate information in your database.

Copyright 2003 by Randy Dyess, All Rights Reserved

Make sure you purchase your copy of Transact-SQL Language Reference Guide from my website www.TransactSQL.Comtoday to learn more about Transact-SQL by reviewing the more than 1200 examples contained within the book

Pages: 1 2




Related Articles :

  • No Related Articles Found

3 Responses to “How to Identify and Delete Duplicate SQL Server Records”

  1. –Another idea using ROW_NUMBER()
    Delete MyTable
    Where Id IN
    (
    Select T.Id FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY UniqueColumn ORDER BY Id) AS RowNumber FROM MyTable
    )T
    WHERE T.RowNumber > 1
    )

  2. I suggest the following code instead..please let me know if there are any issuesd with this
    /**********************************************
    Example of a simple duplicate data delete script.
    **********************************************/
    /**********************************************
    Set up test environment
    **********************************************/
    SET NOCOUNT ON
    –Create test table
    IF OBJECT_ID(‘tDupData’) IS NOT NULL
    DROP TABLE tDupData
    GO
    CREATE TABLE tDupData
    (
    lngCompanyID INTEGER
    ,strCompanyName VARCHAR(20)
    ,strAddress VARCHAR(10)
    ,dtmModified DATETIME
    )
    –Create test data
    INSERT INTO tDupData VALUES (1,’CompanyOne’,’Address1′,’01/15/2003′)
    INSERT INTO tDupData VALUES (2,’CompanyTwo’,’Address2′,’01/15/2003′)
    INSERT INTO tDupData VALUES (3,’CompanyThree’,’Address3′,’01/15/2003′)
    INSERT INTO tDupData VALUES (2,’CompanyTwo’,’Address’,’01/16/2003′)
    INSERT INTO tDupData VALUES (3,’CompanyThree’,’Address’,’01/16/2003′)
    – Dup Data
    INSERT INTO tDupData VALUES (1,’CompanyOne’,’Address1′,’01/15/2003′)
    GO
    /**********************************************
    Finish set up
    **********************************************/
    /**********************************************
    Simple duplicate data
    **********************************************/
    –Create temp table to hold duplicate data
    CREATE TABLE #tempduplicatedata
    (
    lngCompanyID INTEGER
    ,strCompanyName VARCHAR(20)
    ,strAddress VARCHAR(10)
    ,dtmModified DATETIME
    )
    –Identify and save distinct records into temp table
    INSERT INTO #tempduplicatedata
    SELECT distinct * FROM tDupData

    –truncate tDupData
    Truncate table tDupData

    –Insert unique records in table
    INSERT INTO tDupData
    Select * from #tempduplicatedata

    Go

  3. Naga vara prasad Reply June 20, 2012 at 11:55 am

    select distinct * into newtablename from oldtablename

    now the newtablename will have no duplicate records

    now simply change the table name(newtablename) by pressing F2 in object explore in sql server

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |