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

No comments:

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 t...