468,107 Members | 1,311 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

How to delete a table with about 2000 rows

vanc
211 Expert 100+
I'm trying to delete a table with just around 2000 rows. What I got is timeout error. I can't use Truncate Table, which is very quick, because I have Trigger with Delete command.
Is there any way to increase timeout "time" in sql server to let my query finish? Or there is better way to do this. Any comment will help.

Expand|Select|Wrap|Line Numbers
  1. My query is just this
  2.  
  3. Delete From aTable
  4.  
  5.  
Dec 18 '07 #1
2 1497
Jim Doherty
897 Expert 512MB
I'm trying to delete a table with just around 2000 rows. What I got is timeout error. I can't use Truncate Table, which is very quick, because I have Trigger with Delete command.
Is there any way to increase timeout "time" in sql server to let my query finish? Or there is better way to do this. Any comment will help.

Expand|Select|Wrap|Line Numbers
  1. My query is just this
  2.  
  3. Delete From aTable
  4.  
  5.  
You could temporarily disable your trigger to allow for the truncation process nd re-enable it?


Expand|Select|Wrap|Line Numbers
  1.  
  2. ALTER TABLE yourdatabasename.dbo.yourtablename disable trigger yourtriggername
  3.  
  4. truncate table yourtablename
  5.  
  6. ALTER TABLE yourdatabasename.dbo.yourtablename enable trigger yourtriggername
  7.  
Jim :)
Dec 21 '07 #2
iburyak
1,017 Expert 512MB
You might want to try this:

Expand|Select|Wrap|Line Numbers
  1. SET ROWCOUNT 1000
  2. go
  3. while (select count(*) from table_name) > 0
  4. BEGIN
  5.    begin tran 
  6.     delete from table_name
  7.    commit tran
  8. END
  9. go
  10. SET ROWCOUNT 0
  11.  
Good Luck.
Dec 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Gordon | last post: by
1 post views Thread by Simon Withers | last post: by
4 posts views Thread by akej via SQLMonster.com | last post: by
16 posts views Thread by robert | last post: by
9 posts views Thread by nnelson | last post: by
9 posts views Thread by Dejan | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.