locked
synatx query to eliminate duplicate records in a table? RRS feed

  • Question

  • Hi Guys,

    I have one table contain 20 million records and about 30 columns. i want to find duplicate and delete the duplicate records. can anyone please provide the example query to eliminate duplicates.

    Regards,

    Chinni_123

    • Moved by Jonathan KehayiasMVP Thursday, November 8, 2012 12:58 PM TSQL Question (From:SQL Server Database Engine)
    Thursday, November 8, 2012 11:29 AM

Answers

  • Chinni,

    There could be many ways, the following is one of the way to get rid of duplicate rows.

    -- Create a table
    CREATE TABLE t1 (
    	col1 INT
    	,col2 INT
    	,col3 CHAR(50)
    	)
    
    --Insert some duplicate records
    INSERT INTO t1 VALUES (1,1,'Manish')
    INSERT INTO t1 VALUES (1,1,'Manish')
    INSERT INTO t1 VALUES (1,2,'Sql server')
    
    --Verify the records
    SELECT * FROM t1
    
    --Fetch and save the duplicate records into temporary table
    SELECT *
    INTO t2
    FROM t1
    GROUP BY col1
    	,col2
    	,col3
    HAVING COUNT(*) > 1
    
    SELECT * FROM t2
    
    --Delete duplicate records from original table
    DELETE
    FROM t1
    FROM t1
    	,t2
    WHERE t1.col1 = t2.col1
    	AND t1.col2 = t2.col2
    	AND t1.col3 = t2.col3
    
    --Insert the delete data back
    INSERT INTO t1 SELECT * FROM t2
    
    --Verify
    SELECT * FROM t1
    
    --Cleanup
    DROP TABLE t1
    DROP TABLE t2
    
    


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Iric Wen Monday, November 19, 2012 9:18 AM
    Thursday, November 8, 2012 11:32 AM