CODEWITHSUNDEEP
sql-server
Surendra
Surendra

Reputation: 237

Ignore the SQL errors exceptions while inserting and continue the insertion in SQL Server

I had data in one #table containig particular rows and I need to insert all those rows into an original database table. I had written the query as below

insert into tbl_user select * from #tblExcelData

#tblExcelData contains multiple rows which are going to insert in tbl_user.

But while insertion due to a one-row foreign key exception is fired and after that insertion is stopped.

I want to continue this insertion for other rows. It is ok that the faulty Row which is the reason for the exception is not get inserted but other rows should continue.

Is there any way in SQL Server to do this?

Upvotes: 2

Views: 2025

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

Far as I know, you can't; a DML statement is executed completely or not at all, and in the case of an error it's not at all.

What you can (and should) do, is select only data you can insert.

Something like:

insert into tbl_user 
select * 
from   #tblExcelData x
inner join tbl_with_FK y
        on y.PK = x.FK

Upvotes: 3

Related Questions