asked
Dealing with constraint error 2627
I apologize for asking frequent question. I have tried multiple solutions suggested. I am getting the message very clear but why does the processing not continue? I have a Unique Constraint on field. I can change the constraint if that is the issue. Begin Try Begin Tran Insert Into OFACVestalOut Select * From ( Select a.CUName, a.CIFkEY, v.Addrline From allcif As a Join #vessel As v On a.CUName Like '%' + v.Addrline + '%' ) As Display End Try Begin Catch SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; Rollback Tran End Catch Addendum: I used Rollback as a last attempt to get it to work. I need it to ignore error and proceed. I know the dups are present and why. thanks DataJoe
Are you looking to have the process continue after error? Currently you have it configured to rollback the transaction, which it does upon the violation of the unique constraint. The insert is has attempting to insert a record that violates it, so either you fix the data or adjust the unique constraint to allow which ever record(s) that are causing the violation.
You can use (at your own risk. Read up about it) xact_abort or IGNORE_DUP_KEY option on your index.
The SQL error 2627 triggers commonly on tables that contain PRIMARY KEY constraints. Whenever we try to insert a new record into such tables and the key of the record being inserted already exists in the table, it triggers the 2627 error
Greetings,
Will
20 People are following this question.
