Post reply

delete rows in loops of 5000 rows

  • July 17, 2012 at 4:05 pm

    #264312

    i need to delete rows from sql table. it has more than 30 million rows.

    i need to keep rows with datetime column >7/8/12

    please provide script to delete in loops of 5000 rows. thanks

  • July 17, 2012 at 4:12 pm

    #1513933

    a simple google search will yeild several methods this is one:

    DECLARE @Rowcount INT = 1

    WHILE @Rowcount > 0

    BEGIN

    DELETE TOP (5000)

    FROM Tally

    SET @Rowcount = @@ROWCOUNT

    END

    and another:

    DECLARE @Rowcount INT = 1

    SET ROWCOUNT 5000

    WHILE @Rowcount > 0

    BEGIN

    DELETE

    FROM Tally

    SET @Rowcount = @@ROWCOUNT

    END

    and one more:

    SET ROWCOUNT 5000

    WHILE 1=1

    BEGIN

    DELETE

    FROM Tally

    IF @@ROWCOUNT = 0

    BREAK

    END

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • July 17, 2012 at 4:38 pm

    #1513938

    Read this: http://www.sqlservercentral.com/articles/T-SQL/67898/

    Since you are using SQL Server 2008, stay away from using @@rowcount to control your batches.

  • July 17, 2012 at 7:49 pm

    #1513973

    thanks. I am going to use 3rd one. thanks again.

  • July 17, 2012 at 8:24 pm

    #1513977

    And using set rowcount is not a good habit to learn now. It will no longer affect insert, update or delete in the NEXT version of sql. It is better to learn a different way like the way Lynn pointed to in his article.

    http://msdn.microsoft.com/en-us/library/ms188774%28v=sql.105%29.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 17, 2012 at 9:44 pm

    #1513981

    Lynn Pettis (7/17/2012)

    Read this: http://www.sqlservercentral.com/articles/T-SQL/67898/

    Since you are using SQL Server 2008, stay away from using @@rowcount to control your batches.

    I guess I'm missing it. Why stay away from @@RowCount in 2k8?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 17, 2012 at 9:44 pm

    #1513982

    laddu4700 (7/17/2012)

    i need to delete rows from sql table. it has more than 30 million rows.

    i need to keep rows with datetime column >7/8/12

    please provide script to delete in loops of 5000 rows. thanks

    How many rows do you expect to delete from those 30 million rows?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 17, 2012 at 11:17 pm

    #1514002

    Jeff Moden (7/17/2012)

    Lynn Pettis (7/17/2012)

    Read this: http://www.sqlservercentral.com/articles/T-SQL/67898/

    Since you are using SQL Server 2008, stay away from using @@rowcount to control your batches.

    I guess I'm missing it. Why stay away from @@RowCount in 2k8?

    ROWCOUNT is being depreciated in regards to controlling the number of records affected by a select, update, or delete. New development should use the TOP () keyword. @@ROWCOUNT will still return the number of rows affected.

  • July 18, 2012 at 2:23 am

    #1514064

    Lynn Pettis (7/17/2012)

    Jeff Moden (7/17/2012)

    Lynn Pettis (7/17/2012)

    Read this: http://www.sqlservercentral.com/articles/T-SQL/67898/

    Since you are using SQL Server 2008, stay away from using [font="Arial Black"]@@rowcount [/font]to control your batches.

    I guess I'm missing it. Why stay away from @@RowCount in 2k8?

    ROWCOUNT is being depreciated in regards to controlling the number of records affected by a select, update, or delete. New development should use the TOP () keyword. @@ROWCOUNT will still return the number of rows affected.

    I knew about ROWCOUNT being deprecated but you said "stay away from using [font="Arial Black"]@@RowCount [/font]to control your batches" and I thought I'd missed something.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 18, 2012 at 9:17 am

    #1514286

    Jeff Moden (7/18/2012)

    Lynn Pettis (7/17/2012)

    Jeff Moden (7/17/2012)

    Lynn Pettis (7/17/2012)

    Read this: http://www.sqlservercentral.com/articles/T-SQL/67898/

    Since you are using SQL Server 2008, stay away from using [font="Arial Black"]@@rowcount [/font]to control your batches.

    I guess I'm missing it. Why stay away from @@RowCount in 2k8?

    ROWCOUNT is being depreciated in regards to controlling the number of records affected by a select, update, or delete. New development should use the TOP () keyword. @@ROWCOUNT will still return the number of rows affected.

    I knew about ROWCOUNT being deprecated but you said "stay away from using [font="Arial Black"]@@RowCount [/font]to control your batches" and I thought I'd missed something.

    Probably should have been more clear in my posting then, sorry for that. I will strive to be more explicit in the future.

  • July 18, 2012 at 9:56 am

    #1514319

    No problem. I was just confused. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 19, 2012 at 6:41 am

    #1514805

    Incidentally there is also a trace flag you can use to stop the exclusive table lock escalation kicking in at 5000 Rows (try Books on line for it)

    another option for deleting most of the data from a very large table is to Select into a new table the rows you want to keep then drop the original table followed by a rename of your temp table to the old name. ( you might have to watch out for permissions and dependancies but its an efficient method in the right scenarios.

  • July 20, 2012 at 9:05 am

    #1515585

    Careful, none of the answers given test for your Date requirement!

  • July 20, 2012 at 9:12 am

    #1515593

    bdloving 4446 (7/20/2012)

    Careful, none of the answers given test for your Date requirement!

    That's because the best we could do is pseudocode, we never got any ddl to work with. I doubt that anybody would think the delete from Tally was really what the OP wanted. 😀

    DECLARE @Rowcount INT = 1

    WHILE @Rowcount > 0

    BEGIN

    DELETE TOP (5000)

    FROM [SomeTable]

    Where [SomeDateColumn] < '7/8/2012'

    SET @Rowcount = @@ROWCOUNT

    END

    Now we have more accurate pseudocode.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply