I may have misunderstood your intention with this code, so could I just check?

You appear to be selecting data from scd.dbo.semp1 where Statuscode is 1, into @testingtable. Then you select all of these rows, one at a time in a WHILE loop into @demp1. Then you select all rows from @demp1 into demp1.

Is that correct? If so, I don't understand why you can't just select from scd.dbo.semp1 into demp1 and miss out all of the WHILE loop in the middle.

Because none of your tables have constraints I can't see how @FailedRows would ever get used, except for possible conversion issues, but resolve that issue by ensuring source and target data types are the same (Empname is varchar(200) in @testingtable but only varchar(20) in @demp1, for example), or convert them correctly in the code.