January 9, 2004 at 5:14 pm
Hello,
I have an INSERT statement that can at times generate the following error:
Server: Msg 3604, Level 16, State 1, Line 2
Duplicate key was ignored.
This 'error' occurs because the table that recieves the data has a Unique Nonclustered Index on it With IGNORE_DUP_KEY. The message does not really indicate an error, but I need to handle this in such a way that if the insert would raise this error, that the error is not actually raised. If the error is anything other than 3604, the transaction should be rollled back, and the error reported.
The following is the Insert code:
******************************
INSERT #tblMMStudentTestScoresTEMP
(Permnum, TestShortName, TestScore)
SELECT
SD.Permnum,
TT.TestShortName,
"TestScore" = 100 * SUM(CAST(TS.Score AS INT))/COUNT(TS.Score)
FROM
Teacher_Data_Main TD
INNER JOIN Student_Data_Main SD ON TD.TeacherID=SD.TeacherID
INNER JOIN tblRC_StandardsTestScores03_04 TS ON TS.Permnum=SD.Permnum
INNER JOIN tblTests TT ON TT.TestShortName = TS.TestShortName
WHERE
TD.FirstName = 'Becky'
AND
TD.LastName = 'Clark'
GROUP BY SD.Permnum, TT.TestShortName
*********************************
How do I work in the transaction and error handling code for this INSERT to meet my requirement? My SQL Server 2000 database is set up to Autocommit Transactions.
Thanks for your help!
CSDunn
January 9, 2004 at 7:37 pm
Error 3604 is an oddball, at least on my server, because it does not set @@ERROR, even though the severity level of the message is 16. There is no way to keep SQL from raising the error, but your client software can trap for it and ignore it. I'm not sure what else you can do. It would be nice if Microsoft would change the severity level of 3604 from 16 to 10. The weird thing is that the severity level is already set to 10 in master..sysmessages, but SQL still raises it with severity 16, which is not entirely unexpected, since even in our own RAISERROR statements we get to pick the severity.
All that to say this: I think you're just going to have to get your client to ignore the error. Either that or pre-process the data in such a way that you don't attempt to insert the duplicate values.
Hope this helps,
Chris
January 10, 2004 at 8:40 am
This errormessage is not an error but a warning and your client has to treat it as a warning.
If you really want to avoid be shure not to insert the duplicate values by checking the data before inserting into the production table or you insert first into a temp table, you remove the unwanted rows, check against the production table and insert the right data.
Or as I've already said just ignore the warning message
Bye
Gabor
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply