March 12, 2004 at 9:29 am
We've been using the statement below to delete rows in a certain table. The row count keeps getting larger and larger and I've been asked to break down the deletes. I need to do no more than 100,000 at a time and I need to commit every 1000. I wasn't sure if I should use a cursor or IF, Else statement.
Here is the current statement.
declare @deleteconsumerID TABLE (
ConsumerID int NOT NULL )
insert into @deleteconsumerID
select a.consumer_id from consumer.dbo.acct a(nolock)
left join con.dbo.con c(nolock) on a.consumer_id = c.consumer_id
left join age.dbo.lm l(nolock) on a.consumer_id = l.consumer_id
left join con.dbo.part_data p(nolock) on a.consumer_id = p.consumer_id
where committedyn = '?' and c.consumer_id is null and l.consumer_id is null
and p.consumer_id is null and datediff(day, a.created_on, getdate()) > 365
delete from con.dbo.acct where consumer_id in (select * from @deleteconsumerid)
March 12, 2004 at 2:02 pm
not sure if I understand the issue correctly but would using SET ROWCOUNT be suffice in this situation. what is the reason behind the need to break down the delete statement by 100,000 and then by 1,000 records?
March 12, 2004 at 2:09 pm
We don't want to process more than 100,000 rows at a time because the job runs long. We need a commit every 1000 so the table is released between deletes.
I did have the setrow count 100000 but couldn't pull the table variable into a loop to process 1000 commits.
March 12, 2004 at 2:22 pm
How about:
Declare @deleteconsumerid table (pk int identity(1,1), cons_id Int), @rows Int
Set @rows = 1000
While (Select count(*) From @deleteconsumerid) > 0
Begin
delete A from con.dbo.acct A Join @deletesconsumerid D On A.consumer_id = D.cons_id where D.pk <=@rows
Delete from @deleteconsumerid where pk <= @rows
Set @rows = @rows + 1000
End
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2004 at 2:39 pm
Thanks for your help, I tried the code above added with my insert statement, but not sure I'm declaring my table variable in the right spot. Here is the error message I received and the script I ran. Any ideas?
Server: Msg 137, Level 15, State 2, Line 19
Must declare the variable '@deletesconsumerid'.
Declare @deleteconsumerid table (pk int identity(1,1), cons_id Int)
declare @rows Int
insert into @deleteconsumerID
select a.consumer_id from con.dbo.acct a(nolock)
left join con.dbo.con c(nolock) on a.consumer_id = c.consumer_id
left join age.dbo.lm l(nolock) on a.consumer_id = l.consumer_id
left join con.dbo.part_data p(nolock) on a.consumer_id = p.consumer_id
where committedyn = '?' and c.consumer_id is null and l.consumer_id is null
and p.consumer_id is null and datediff(day, a.created_on, getdate()) > 365
Set @rows = 1000
While (Select count(*) From @deleteconsumerid) > 0
Begin
delete A from con.dbo.acct A Join @deletesconsumerid D On A.consumer_id = D.cons_id where D.pk <=@rows
Delete from @deleteconsumerid where pk <= @rows
Set @rows = @rows + 1000
End
March 12, 2004 at 6:56 pm
Typo on my part down in the delete satement. It says @deletesconsumerid instead of @deleteconsumerid.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 15, 2004 at 3:58 pm
Thanks for all your help.
Susan
March 16, 2004 at 2:25 am
Why bother with the TABLE variable?
declare @rows int
set @rows = 0
while (@rows < 100000)
begin
delete con.dbo.acct
from (
select TOP 1000 a.consumer_id as [ID]
from
consumer.dbo.acct a(nolock)
left join con.dbo.con c(nolock)
on a.consumer_id = c.consumer_id
left join age.dbo.lm l(nolock)
on a.consumer_id = l.consumer_id
left join con.dbo.part_data p(nolock)
on a.consumer_id = p.consumer_id
where
committedyn = '?'
and c.consumer_id is null
and l.consumer_id is null
and p.consumer_id is null
and datediff(day, a.created_on, getdate()) > 365
) as [RANGE]
where
con.dbo.acct.consumer_id = [RANGE].[ID]
set @rows = @rows + 1000
end
Peter Evans (__PETER Peter_)
March 31, 2004 at 11:03 am
how do I apply this same solution to commit every 1000 inserts??
March 31, 2004 at 11:49 am
The example is commiting every 1000 records.
My requirments were bring in no more than 100,000 rows then execute the inserts 1000 at a time.
Hopefully that's what you were referring to.
Thanks
Susan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply