sql server - How to continue cursor loop even error occured in the loop -

i want insert rows of 1 table another. have below procedure.

alter procedure loaddata_a  begin  truncate table stident_a declare @sid int declare @sname varchar(50) declare @sub varchar(50) declare @marks int  declare load_data cursor fast_forward   select sid,sname,sub,marks  student   open load_data    fetch next load_data @sid,@sname,@sub,@marks  while @@fetch_status = 0    begin     begin transaction      insert stident_a(sid,sname,sub,marks) values (@sid,@sname,@sub,@marks)      if @@error != 0     begin         rollback transaction         return     end     else     begin         commit transaction     end     fetch next load_data @sid,@sname,@sub,@marks end  close load_data    deallocate load_data  end 

whenever row fails insert, cursor fails , stops there, need continue. how that?

apparently, you've got return statement after rollback transaction. have tried removing it?

although rewrite body using try/catch instead, this:

... while @@fetch_status = 0    begin     begin try         insert stident_a(sid,sname,sub,marks)         values (@sid,@sname,@sub,@marks);     end try     begin catch       -- section must have statement,       -- so, why not log erroneous data screen @ least?       print @sid;       print @sname;       print @sub;       print @marks;       print '';  -- empty line delimiter       -- or, perhaps, table?       --insert somefaillog (sid,sname,sub,marks)       --values (@sid,@sname,@sub,@marks);     end catch;     fetch next load_data @sid,@sname,@sub,@marks; end; ... 

but if know may cause inserts fail, might better come single statement produce valid data insert.

for instance, if issue sids in student exist in stident_a , need omit them, try following instead of procedure:

insert stident_a (sid, sname, sub, marks) select s.sid, s.sname, s.sub, s.marks student s left join stident_a on s.sid = a.sid a.sid null ; 

if specify may issue while transferring data, might able finding efficient solution that.

---

update addressing comments

if issue maximum length of sname in stident_a less of same name column in student , values may not fit, use filter (a where clause) limit inserted rows actual length of sname not exceed value:

insert stident_a (sid, sname, sub, marks) select sid, sname, sub, marks len(sname) <= @maxlength ; 

you query metadata determine maximum length of required column. there various ways, 1 use sys.objects system catalog:

declare @maxlength int; select @maxlength = max_length sys.objects object_id = object_id('stident_a')   , name = 'sname' ; 

to determine rows not inserted:

insert stident_a (sid, sname, sub, marks) select sid, sname, sub, marks len(sname) > @maxlength ;