Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Deleting 500K rows at a time

Traitée Deleting 500K rows at a time

  • Friday, April 13, 2012 4:15 PM
     
     
    Sign In to Vote
    0

    Hello,

    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?

    Thanks,

    KK

     

All Replies

  • Friday, April 13, 2012 4:21 PM
     
     
    Sign In to Vote
    0

    Something like this

    Declare @RowCnt int

    SET @Rowcnt = 1

    WHILE @RowCnt > 0

    DELETE TOP 500,000

    FROM

    Where ...

    SET @RowCnt = @@Rowcount

    END

    Chuck

     
  • Friday, April 13, 2012 4:22 PM
     
     
    Sign In to Vote
    0
    why not delete all the time?
     
  • Friday, April 13, 2012 4:23 PM
     
     
    Sign In to Vote
    0
    why not delete all the time?

    Because he'll blow up his tran log having that many records in one transaction

    Chuck

     
  • Friday, April 13, 2012 4:30 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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").

    The Data Loading Performance Guide

    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).

    David.


     
  • Friday, April 13, 2012 4:35 PM
     
     
    Sign In to Vote
    0

    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 PM
     
     
    Sign In to Vote
    0
    create 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.