General information
Forum:
Microsoft SQL Server
>Is there a simple way to find all duplicate records for a certain set of fields in a table and then mark one field as duplicate to indicate them.
>
>Or does one usually create some sort of cursor and handle it with indexes and record type processing?
Terry,
Create a temp table indentical to the table you are trying to remove duplicates from:
CREATE TABLE #tablenodups
(field1 int...)
Then, create a unique index on the fields in question....
CREATE UNIQUE INDEX removedups ON #tablenodups (field1,...) WITH IGNORE_DUP_KEY
Then, insert all the records from your table into your temp table...
INSERT #tablenodups
SELECT * from mytablewithdups
ANyrows that don't meet the index will be rejected, but the insert will continue due to the WITH IGNORE_DUP_LEY hint.
BTW: This method comes from Guru's Guide to T-SQL, so I can't take credit for it.
BOb
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only