December 15, 2016 at 12:06 pm
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
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) A socialist is someone who will give you the shirt off *someone else's* back.
December 16, 2016 at 7:54 am
Awesome, thx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply