October 4, 2001 at 11:41 pm
hi u all, i know this is a very very small problem, and is giving me a rough time, i have created a proc to delete all dead records from my database and insert it into another table, the thing is deleting the dead records from table but not inserting it into another one
here is the proc ::
create procedure daily
as
select * from testjob where lastdateapplication < getdate()
insert into deadjobs select * from testjob where lastdateapplication < getdate()
delete from testjob where lastdateapplication < getdate()
know this is a very childish problem but will be glad if u could answer it
TIA,
keyur
October 5, 2001 at 7:31 am
Couple notes.
1) No reason to do the initial select, I doubt you would want it a resultset returned to you.
2) For the insert, does it work if you run it standalone? This is my opinion, but I prefer to fully qualify inserts, like this:
Insert into (field1, field1) select field1, field from ...
3) There is a gap in your logic that in the worst case could hurt you. Say that when you do the insert there are 10 rows matching the criteria. But by the time it executes and you do the delete, a user has added an 11th row - it will be deleted but not archived.
4) If you use this only occasionally, stored proc is good. If you want to do this a lot, better and safer to use a trigger, then you don't have to worry about item #3.
Andy
October 5, 2001 at 2:23 pm
wrap this in a transaction and use a stored proc. Keep triggers to a minimum.
Steve Jones
October 5, 2001 at 11:16 pm
thanks andy and steve for ur feedbacks,
well i have to run the storedproc every morning when the data entry has not started
and the select is running alone also it shows no problem,
the prob is that the proc is deleting the recs but not inserting it into the archives table
thanks anyway for ur feedback,
will get back to u if the proc ticks
October 6, 2001 at 12:07 am
quote:
the prob is that the proc is deleting the recs but not inserting it into the archives table
When you run the stored procedure manually, it returns 0 row(s) affected in the messages with respect to the insert? Or what are you seeing?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
October 6, 2001 at 12:10 am
Andy and Steve, thanks for the feedbacks
but finally this code for proc worked perfectly
create procedure daily
as
insert into deadjobs1 select * from testjob1 where lastdateapplication < getdate()
delete from testjob1 where lastdateapplication < getdate()
thanks once again
keyur
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply