How to remove delete duplicate records in a table with no keys defined

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  May 20, 2008 at 6:24 am

  #817792

  ALTER Table to add an additional column with IDENTITY Enabled. Drive your logic based on the new column and once the duplicate records or deleted, drop the column.

  Prasad Bhogadi
  www.inforaise.com

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  May 20, 2008 at 7:02 am

  #817830

  [font="Verdana"]1. Select the distint records from original table and insert them into temp table.

  2. Truncate the original table and insert the al records from Temp table into original table.

  3. Then drop the temp table.

  Mahesh[/font]

  MH-09-AM-8694

• tbeadle

  SSCommitted

  Points: 1635

  More actions

  May 20, 2008 at 12:09 pm

  #818139

  [font="Arial"]

  May I suggest use set rowcount 1...for example:

  create table tlb_test

  ( tname varchar(20) null,

  tother int null )

  insert tlb_test

  select 'this', 1 union all

  select 'This', 2 union all

  select 'this', 1 union all

  select 'nope', 3

  select * from tlb_test

  select * from tlb_test

  where tother = 1

  and tname = 'this'

  set rowcount 1

  go

  delete tlb_test

  where tother = 1

  and tname = 'this'

  go

  set rowcount 0

  go

  [/font]