question

DataJoe avatar image
DataJoe 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
constraints
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

1 Answer

· Write an Answer
Waqar_lionheart avatar image
Waqar_lionheart answered
You can use (at your own risk. Read up about it) xact_abort or IGNORE_DUP_KEY option on your index.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.