Solved

how do you remove duplicate records? (mssql)

Posted on 2008-06-20
4
312 Views
Last Modified: 2008-06-20
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
Comment
Question by:darkmooink
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21830268
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
 
LVL 3

Author Comment

by:darkmooink
ID: 21830445
no they are identical, there is no unique identifier
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 125 total points
ID: 21830523
SELECT DISTINCT col1, col2...
INTO #Temp
FROM urTable
TRUNCATE TABLE urTable
INSERT INTO urTable
SELECT * FROM #Temp
0
 
LVL 3

Author Comment

by:darkmooink
ID: 21831436
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Shop sales Actual vs Forecast 2 26
Group by and order by clause 28 36
Test a query 23 18
MSSQL: Wrong result in a query (Nulls) 6 0
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now