Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
missMac
Posting Yak Master
124 Posts |
 Posted - 2008-12-04 : 14:51:10
|
| Hi guys,1. How do i delete duplicate records ?Which is better during an insert.(a) To run an insert with a sub query to search if not exists(b) Run an insert and then delete record counts having more than 1 entry ?insert into dbo.PhoneNumbers (Username, Number, NickName, Group_Name )select distinct @user, recepient, recepient, @mergeID from filter fwhere Username = @user and BatchNumber = @recipientand not exists(select 1 from dbo.Phonenumbers where number = f.number)thanksMM |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-12-04 : 15:33:10
|
| 1. Insert the record to a temp table using max on each field (and a group by obviously), delete both rows and reinsert the row from the temp table.Option a is the best method and has the least overhead and maintenece cost. |
 |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-12-05 : 14:23:28
|
| thanks Bruv |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-05 : 23:39:05
|
quote:
Originally posted by missMac
Hi guys,1. How do i delete duplicate records ?Which is better during an insert.(a) To run an insert with a sub query to search if not exists(b) Run an insert and then delete record counts having more than 1 entry ?insert into dbo.PhoneNumbers (Username, Number, NickName, Group_Name )select distinct @user, recepient, recepient, @mergeID from filter fwhere Username = @user and BatchNumber = @recipientand not exists(select 1 from dbo.Phonenumbers where number = f.number)thanksMM
what happens if select query itself contain duplicate records in first case?i think if select query contain duplicate values, then (b) is solution. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-12-06 : 08:19:41
|
| No, just change the select to not have duplicates. If the select has duplicates, you are doing something wrong. |
|
|
|
|
|
|
|
|
How do i delete duplicate records ?