Friday, April 13, 2012 4:15 PM
I have to implement a step in SSIS where I can delete 500K rows at a time from a table until all the rows are deleted. So if I have 2 million rows in the table this query should run 4 times and delete the 2 mil. rows. How can I do this?
Friday, April 13, 2012 4:21 PM
Something like this
Declare @RowCnt int
SET @Rowcnt = 1
WHILE @RowCnt > 0
DELETE TOP 500,000
SET @RowCnt = @@Rowcount
Friday, April 13, 2012 4:22 PMwhy not delete all the time?
Friday, April 13, 2012 4:23 PM
why not delete all the time?
Because he'll blow up his tran log having that many records in one transaction
Friday, April 13, 2012 4:30 PM
Hi, if performance is the main concern check the article below (and search for "Deleting a Large Amount of Rows in a Partition or Table").
One other option is to use Modulo in some numeric ID, for example:
-- step #1 delete from tableA where ID % 4 = 0 -- step #2 delete from tableA where ID % 4 = 1 -- step #3 delete from tableA where ID % 4 = 2 -- step #4 delete from tableA where ID % 4 = 3
And (obviously) adding some other logic to the where clause (to delete only the intended rows).
Friday, April 13, 2012 4:35 PM
I believe that the best way is to delete the records in parts.
use the execute sql task.
delete from table where id in ( select top 10 from table )
- Edited by Bruno.Costa Friday, April 13, 2012 4:36 PM
Friday, April 13, 2012 10:03 PMcreate a stored procedue and pass the delete count as a parameter. use while loop until all the total rows are deleted and commit based on delete count.