Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

how do you remove duplicate records? (mssql)

i have a 70,000 record table but i think it should only have 35,000 because all of the records i looked at (when in id order) has a twin underneath it. is there an easy way of removing one of the records but keeping the other?
the only way i can think of doing it is to delete them both and then insert one again.
0
darkmooink
Asked:
darkmooink
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is there any column that you can use to distinguish the 2 records?
if yes: it's easy
if not: it will not be straightforward...
0
 
darkmooinkAuthor Commented:
no they are identical, there is no unique identifier
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT DISTINCT col1, col2...
INTO #Temp
FROM urTable
TRUNCATE TABLE urTable
INSERT INTO urTable
SELECT * FROM #Temp
0
 
darkmooinkAuthor Commented:
thankyou aneeshattingal that would have worked (i tryed it on test data) but i did something that deleted the whole table's data.  we should have the data somewhere else so it will hopefully only be missing for a week and a half till my manager gets back and finds the data again.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now