Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: sql sever 2005 delete statement

    Hi ,
    Can anyone help with a delete process for sql server 2005?
    We are in the process of deleting a large number of rows from muliple tables. Since we are not deleting all the rows from any one table we cant just truncate the table.
    Our process will use delete statements and delete based on ids. Since this might run for a day should we use delete with rowlock? Should we also put the delete statments in one transaction? Any other ideas would be apprectiated.

    Thanks again for your advise.

    Rebecca

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Put your delete in a loop like this so you don't crap out your TLog with one huge transaction.


    BEGIN
    DECLARE @N INT
    SET @N = 3000
    DECLARE @RowCount int
    SET @RowCount = 1

    WHILE @RowCount > 0
    BEGIN
    SET ROWCOUNT @N
    DELETE FROM Table WHERE condition
    SET @RowCount = @@RowCount
    END
    END

  3. #3
    Join Date
    Oct 2008
    Posts
    56
    Thanks for the advice.

    Is there an implicit commit when you don't use a
    begin transaction ... commit sequence? Does it commit with every affected row?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •