How to skip data which violates foreign key constraint and insert the remaining data in SQL Server?-CodePudding

I am using a spring boot program to load some data to a child table in MS SQL server. I am getting the data from a service and looping the insert statement for each item. There is some corrupted data which violates foreign key constraints. I want to skip the data which violates FK constraints and continue inserting remaining data.

I am using the below insert query to do the same

begin try INSERT INTO [dbo].[TABLENAME](.....column names......)VALUES(.....) end try begin catch end catch;

The try catch is handling the FK constraint exception that is thrown and letting the program continue to execute other records in the loop without any issue but it is also not throwing error for any other constraint violation. Is there a way to specifically mention error type in the try catch so it handles only FK constraint.

I was able to do similar constraint specific catch in Oracle using exception when dup_val_on_index then in other program. Any help is greatly appreciated!

CodePudding user response：

You can use @@ERROR, ERROR_NUMBER() or ERROR_MESSAGE() within begin catch end catch block.

Example:-

Begin try
    INSERT INTO [dbo].[TABLENAME](.....column names......)VALUES(.....)
End try
Begin catch
    IF ERROR_NUMBER() != 547
    -- IF @@ERROR != 547
    -- IF ERROR_MESSAGE() Not like '%The INSERT statement conflicted with the FOREIGN KEY constraint%'
Throw
End catch