January 4, 2017 at 2:06 pm
I have a stored procedure - in this how can I capture the record into error table .
where I have to keep condition that it errors out?
Declare Cursor1 cursor static for
select columns from temp table
open cursor1
fetch next from cursor1 into
@col1,
@col2
while @@fetch_status = 0
begin
begin tran
insert into main table( col1,col2)
select col1,col2 from temp table
if @@error<> 0
begin
rollback tran
insert into errortbl (col)
values('fff')
exec process another exception procedure
end
if @@error= 0
begin
set inserted = ins+1
commit tran
end
end
close cursor1
deallocate cursor1
January 4, 2017 at 2:50 pm
Just out of curiosity why are you doing this,
insert into main table( col1,col2)
select col1,col2 from temp table
In a cursor where you're setting from the same temp table?
January 4, 2017 at 4:43 pm
we have a job(ssis package) where it load data from different source and destination is temp table. from temp table I have to move to main table.
selecting rec from temp table where ID not in (select ID from main table where code < > -2)
those rec will insert into main table.
Now I want to produce an exception rec that loads into exception table how to do this?
January 5, 2017 at 9:41 am
mcfarlandparkway (1/4/2017)
we have a job(ssis package) where it load data from different source and destination is temp table. from temp table I have to move to main table.selecting rec from temp table where ID not in (select ID from main table where code < > -2)
those rec will insert into main table.
Now I want to produce an exception rec that loads into exception table how to do this?
The row-level error handling is much better in SSIS than it is in T-SQL. Since you are already using SSIS as part of your process, you should use SSIS for this rather than trying to do it in T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 5, 2017 at 10:45 am
mcfarlandparkway (1/4/2017)
we have a job(ssis package) where it load data from different source and destination is temp table. from temp table I have to move to main table.selecting rec from temp table where ID not in (select ID from main table where code < > -2)
those rec will insert into main table.
Now I want to produce an exception rec that loads into exception table how to do this?
Then isn't your exception report(assuming you don't have any other conflicts with the main table like primary keys or constraints) just
selecting rec from temp table where ID in (select ID from main table where code != -2)
And why does that need a SP and cursor at all?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply