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

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

JohnM avatar image JohnM commented ·
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 ·
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

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

Willjoe2442 avatar image
Willjoe2442 answered

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

10 |1200

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.