locked
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.

     

    Regards

    Ram

    Tuesday, August 31, 2010 11:00 AM

Answers

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 ..............

    http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html


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

    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.

    Regards

    Ram

    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:


       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb
          Clifford Dibble
      
       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2
          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. 


    AMB

    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 '
    exec(@sql)
    

     

     

    Tuesday, August 31, 2010 2:02 PM