How to ensure the rest of the records will be inserted even if there is an error

Hi,
I have a sql statement that perform bulk insert into another table.
How can I ensure that if an error occurs, maybe due to primary key
constraint, the rest of the records will be inserted.
Thanks alot.Hi
Take a look at this example, even this transaction will generate a violation
of primary key constraint the rest of the data will be inserted
Please read SET ARITHABORT commant in the BOL to get a whole picture
create table #t (col int not null primary key)
begin tran
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
commit
select * from #t
drop table #t
"Shelby" <shelby@.singnet.com.sg> wrote in message
news:%23X6PcA6XGHA.3868@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a sql statement that perform bulk insert into another table.
> How can I ensure that if an error occurs, maybe due to primary key
> constraint, the rest of the records will be inserted.
> Thanks alot.
>
>
>|||That depends on the type of error that occures. Fatal Errrors for
example will always abort the entire procedure and rollback all
transactions. There might be a way for non-fatal errors using if-blocks
like
if @.err<>0
// do something else with the datasql