Post reply

Will Cancelling an update that updates 50k rows in a while loop rollback id cancelled

  • December 15, 2016 at 12:06 pm

    #332821

    Hey guys, I have a while loop where I'm using a temp table(actually a normal table in the database named with the prefix "tmp_") to join against another table to update 50k rows at a time. I realized that my temp table didn't have a clustered index leaving it as a heap. I'd like to stop the update, add a clustered index to the tmp table. If I stop the query will it rollback all the updates that's already completed? My code is below:

    While (Select count(*) from dbo.tmp_MyTbl) > 0

    Begin

    Update Top (50000) m

    set MyColumn = f.MyColumn

    from (SELECT TOP 50000 * FROM dbo.tmp_MyTbl WITH (NOLOCK) ORDER BY [PrimeKey_ID] ASC) AS f

    inner join dbo.MyotherTable m on f.PrimeKey_ID = m.PrimeKey_ID

    DELETE FROM dbo.tmp_MyTbl

    WHERE PrimeKey_ID IN (SELECT TOP 50000 PrimeKey_ID FROM dbo.tmp_MyTbl WITH (NOLOCK) ORDER BY [PrimeKey_ID] ASC);

    END

  • December 15, 2016 at 4:05 pm

    #1918308

    No, not unless you have an explicit BEGIN TRANSACTION outside of that code. By default, each statement is automatically committed once it completes.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 16, 2016 at 7:54 am

    #1918411

    Awesome, thx

Viewing 3 posts - 1 through 2 (of 2 total)

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