CODEWITHSUNDEEP
sql-server
user1182553
user1182553

Reputation: 53

sql server skip error on inserting multiple rows at a time

I am doing a batch process. In my process I build a set of insert query statements and will run from my application. I won't use sql transactions and want to skip the statement which throws error.

For example: 

create table test
(
test varchar(20)
)

insert into test (test) values ('test1'); -- 1 row affected
insert into test (test) values ('test2'); -- 1 row affected
insert into test (test) values ('a' + 333); -- This will throw error while executing and i ---want to skip this
insert into test (test) values ('test4'); -- This should be affected as per my requirement

Is it possible such type of process?

Upvotes: 1

Views: 6570

Answers (3)

MUS
MUS

Reputation: 1450

Wrapping each insert statement in try catch block can solve the problem.

Upvotes: 0

gbn
gbn

Reputation: 432451

Like this you can't, unless you do one of

  • submit row by row
  • wrap each INSERT in a TRY/CATCH

bcp and BULK INSERT have a MAXERRORS option which isn't exposed in the .net SQLBulkCopy class, which may be better way to do it...

Upvotes: 2

tobias86
tobias86

Reputation: 5029

You could just surround each INSERT statement with a try-catch block, e.g.

BEGIN TRY
    INSERT INTO test...
END TRY
BEGIN CATCH
     --Do nothing
END CATCH

Upvotes: 2

Related Questions