Duplicate records find in table RRS feed

  • Question


    Hi ALL,

    I want to find the dynamically check the primary key values and remove the duplicate records.. please suggest it.




    Tuesday, August 31, 2010 11:00 AM


All replies

  • Primary Key has a unique index on it, preventing duplicates.

    Or is this a column you want to make PK?

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, August 31, 2010 11:03 AM
  • if not exists (select * from tbl where key=@par)

    insert into ..............


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 31, 2010 11:07 AM

    sorry for inconvience!!


    My table have  duplicate records  i want to select the duplicate records thorugh dynamically..


    in my  may or many not be primary Key.



    Tuesday, August 31, 2010 1:53 PM
  • Please provide sample input data and expected results.  Also, read these two guidelines to posting questions on the Transact SQL Forum:

          Clifford Dibble
          Phil Brammer

    • Proposed as answer by Naomi N Tuesday, August 31, 2010 2:02 PM
    • Marked as answer by Kalman Toth Tuesday, September 7, 2010 5:24 AM
    Tuesday, August 31, 2010 1:56 PM
  • Try:

    -- to select all duplicated rows

    ;with dup as (
    select c1, c2, c3 -- here you put the set of columns defining your key
    from T
    group by c1, c2, c3
    having count(*) > 1
    select A.*
    from T as A inner join dup as B on A.c1 = B.c1 and A.c2 = B.c2 and A.c3 = B.c3;

    -- leave one row per duplicated group and select the rest

    with rs as (
    select *, row_number() over(partition by c1, c2, c3 order by (select NULL)) as rn -- sort using proper columns to avoid undeterminism, other than (SELECT NULL)
    from T
    select *
    from rs
    where rn > 1;

    Be aware of nullability. 


    Some guidelines for posting questions...

    • Marked as answer by Kalman Toth Tuesday, September 7, 2010 5:24 AM
    Tuesday, August 31, 2010 2:01 PM
  • Is column Name will provide in runtime??? Based on that column Name, u need to find the columnNames with duplicate??

    You need to use dynamic sql... see the below example.

    And also when u r asking a question, try to give some examples, your table structure and some sample insert statements,.

    declare @ColumnName varchar(100)
    declare @sql nvarchar(max)
    set @sql = 'select ' + @ColumnName + ' from TableName group by ' + @ColumnName + ' having count(*) > 1 '



    Tuesday, August 31, 2010 2:02 PM