asked
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
As it's proven impossible to ignore this message in 2000 I've amended the dedup process to the following:
http://support.microsoft.com/kb/139444
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.
Thanks for your reply. I probably should have mentioned that ADDRESS_CLEANDUP is a temporary table created at the start of the procedure just before the IGNORE_DUP_KEY index is created. The data is then inserted into the ADDRESS_CLEANDUP thus removing the duplicates. It's then I get the warning message. If I can't suppress this message then I'll have to look at other deduplication method. Unfortunately there are more column in the tables than ADDR1 and POSTCODE. We just dedup on these columns.
15 People are following this question.
