none
Delete top 1000 rows from Table in 2 batches RRS feed

  • Question

  • I need some help writing while loop to delete Data from Table. Table has  5000 rows. I want to delete top 2000 (1000 rows in each batch).

    Table Del (ID Int , Text1 varchar(50), Text2 Varchar(25))

    Delete top 2000 ID's (1000 rows per batch using while loop)


    sami

    Tuesday, June 11, 2019 2:24 PM

Answers

  • WHILE 1 = 1
    BEGIN

       DELETE TOP (1000)
       FROM tbl;

       IF @@ROWCOUNT < 1000 BREAK;

    END

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by arc2013 Tuesday, June 11, 2019 4:20 PM
    Tuesday, June 11, 2019 2:26 PM
    Answerer
  • Hi,

    Try this :

    WHILE @@ROWCOUNT>0 

    BEGIN

    DELETE TOP(1000) FROM Del   

    END



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    • Edited by Ousama EL HOR Tuesday, June 11, 2019 2:29 PM
    • Marked as answer by arc2013 Tuesday, June 11, 2019 4:20 PM
    Tuesday, June 11, 2019 2:28 PM
  • This will loop until it deletes all rows from the table correct? I only want to delete top 2000 (1000 in each batch) and then break.

    sami

    Then use this :

    DECLARE @rn INT=0
    WHILE @rn<2000
    BEGIN
    DELETE TOP(1000)  FROM Del   
    SET @rn=@rn+@@ROWCOUNT
    END


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by arc2013 Tuesday, June 11, 2019 4:20 PM
    Tuesday, June 11, 2019 3:05 PM

All replies

  • WHILE 1 = 1
    BEGIN

       DELETE TOP (1000)
       FROM tbl;

       IF @@ROWCOUNT < 1000 BREAK;

    END

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by arc2013 Tuesday, June 11, 2019 4:20 PM
    Tuesday, June 11, 2019 2:26 PM
    Answerer
  • Hi,

    Try this :

    WHILE @@ROWCOUNT>0 

    BEGIN

    DELETE TOP(1000) FROM Del   

    END



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    • Edited by Ousama EL HOR Tuesday, June 11, 2019 2:29 PM
    • Marked as answer by arc2013 Tuesday, June 11, 2019 4:20 PM
    Tuesday, June 11, 2019 2:28 PM
  • This will loop until it deletes all rows from the table correct? I only want to delete top 2000 (1000 in each batch) and then break.

    sami

    Tuesday, June 11, 2019 2:47 PM
  • This will loop until it deletes all rows from the table correct? I only want to delete top 2000 (1000 in each batch) and then break.

    sami

    Then use this :

    DECLARE @rn INT=0
    WHILE @rn<2000
    BEGIN
    DELETE TOP(1000)  FROM Del   
    SET @rn=@rn+@@ROWCOUNT
    END


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by arc2013 Tuesday, June 11, 2019 4:20 PM
    Tuesday, June 11, 2019 3:05 PM
  • Thank You. I actually Used Break if Rowcount<=2000 and it worked.

    sami

    Tuesday, June 11, 2019 4:20 PM