How To Suppress 'Duplicate Key Was Ignored' Message?
Hi, I'm using a UNIQUE INDEX WITH IGNORE_DUP_KEY in a stored procedure to remove duplicate data on ADDR1 and POSTCODE fields: CREATE UNIQUE INDEX removeduplicates ON ADDRESS_CLEANDUP (ADDR1,POSTCODE) WITH IGNORE_DUP_KEY However when it is run it results in the following error message being displayed: Server: Msg 3604, Level 16, State 1, Line 34 Duplicate key was ignored. Is there anyway to suppress this message in SQL Server 2000? Or a better way to deduplicate in a similar manner that won't produce an error? TIA
My quick answer regarding suppressing the message is that I am not aware of a way to do that. The IGNORE_DUP_KEY option will not actually remove the duplicates. It just affects how future inserts are handled. If that option is not set (the default), the entire insert will fail if even 1 row is a duplicate. With the option set, only duplicate rows fail (with a warning) while the rest are inserted into the table. It is very important to note that the option only applies to new data entered after the option is added (by creating or rebuilding the index). If the table already contains duplicates, you will not be able to create a unique index with or without that option. You could use "SELECT DISTINCT addr1, postcode" to get a list of duplicate values, but you would need to know which ones to delete and which to keep. There are several ways to do this, but here is an example of an easy method (assuming no other columns exist and no foreign keys on the table). SELECT DISTINCT addr1, postcode INTO #distinctones FROM ADDRESS_CLEANDUP ; TRUNCATE TABLE ADDRESS_CLEANDUP; INSERT ADDRESS_CLEANDUP SELECT * FROM #distinctones ; DROP TABLE #distinctones; Good luck. I hope this helps.