How to get rid of duplicates in the table

I have a table which has quite a few duplicates and I cannot create unique
index on it.
First I want to list those duplicate so I can do something about it and
secondly, I want to delete them so I can create index.
ThanksMac,
Please post the DDL including sample data.
Thanks
Jerry
"Mac" <msamani@.comcast.net> wrote in message
news:e9Auq1l1FHA.3524@.tk2msftngp13.phx.gbl...
>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>|||Mac,
Build an index and do a select out all rows and add GROUP BY <COL>
HAVING COUNT(*) > 1. You will then get the rows which are duplicate
based on the <COL>. You can certainly add <COL1>, <COL2>. Then
remove/modify the rows and you should be able to build a unique index.
Be aware you need to do this when the system is quiet otherwise you can
have extra duplicates occur. Also your applicatio may need to be
updated to deal with a UNIQUE index collission.
Shahryar
Mac wrote:
>I have a table which has quite a few duplicates and I cannot create unique
>index on it.
>First I want to list those duplicate so I can do something about it and
>secondly, I want to delete them so I can create index.
>Thanks
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Put an identity on the table
group by your fields and Max identity value, insert that into another table
delete everything from orig table where identity is not in the other table
drop identity
create index
http://sqlservercode.blogspot.com/
"Mac" wrote:
> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>|||Hi Mac.
This is a 4 step solution. First you have to move all dups into a new table,
lets call TEMP_TAB. Second delete all dups from your master table, lets call
TAB_WITH_DUPS. Third move all records from TEMP_TAB to TAB_WITH_DUPS. Forth
dropTEMP_TAB.
For your understanding check following out.
---
-- STEP 0 - Preparing for the test
---
CREATE TABLE TAB_WITH_DUPS (COL_1 VARCHAR(10), COL_2 VARCHAR(10), COL_3
VARCHAR(10), )
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('AA', 'BB', 'CC')
INSERT INTO TAB_WITH_DUPS VALUES ('BB', 'BB', 'AA')
INSERT INTO TAB_WITH_DUPS VALUES ('CC', 'BB', 'CC')
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 1 - Move dups to a new table
---
SELECT
DISTINCT
COL_1,
COL_2,
COL_3
INTO
TEMP_TAB
FROM
TAB_WITH_DUPS
GROUP BY
COL_1,
COL_2,
COL_3
HAVING
COUNT(*) > 1
SELECT * FROM TEMP_TAB
---
-- STEP 2 - Delete dups from the master table
---
DELETE a
FROM TAB_WITH_DUPS a
INNER JOIN TEMP_TAB b
ON a.COL_1 = b.COL_1
AND a.COL_2 = b.COL_2
AND a.COL_3 = b.COL_3
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 3 - Bring data back to the master table.
---
INSERT INTO TAB_WITH_DUPS SELECT * FROM TEMP_TAB
SELECT * FROM TAB_WITH_DUPS
---
-- STEP 4 - Get rid of the newly created table
---
DROP TABLE TEMP_TAB
"Mac" wrote:
> I have a table which has quite a few duplicates and I cannot create unique
> index on it.
> First I want to list those duplicate so I can do something about it and
> secondly, I want to delete them so I can create index.
> Thanks
>
>|||I believe this is a simpler method:
1. SELECT * INTO #Temp1 FROM [TableName]
2. TRUNCATE TABLE [TableName]
3. CREATE UNIQUE INDEX [IndexName] ON [TableName] (ColumnNames] WITH
IGNORE_DUP_KEY
4. INSERT INTO [TableName] (ColumnNames)
SELECT (ColumnNames)
FROM #Temp1
Mike