Tuesday, November 17, 2009

row by row insert in sql server

Row by row insert and update using cursors and catch the error rows in error table.

This is a sample script template to simulate row by row insert in SQL Server and redirect the error rows into an error table.

create table source(iid int);
create table dest(iid int primary key)create table err(iid int )
insert into source select 1 union select 2 union select 3 union select 4;

insert into dest select 3;

select * from source;
select * from dest;
select * from err;

using cursors

declare mycur cursor for select iid from source
declare @iid int

open mycur 
fetch next from mycur into @iid
while @@fetch_status = 0
begin try  
insert into dest select @iid   
end try  

begin catch  
insert into err select @iid  
end catch    
fetch next from mycur into @iid 
continue end
close mycur
deallocate mycur

Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...