February 4, 2010 at 1:17 pm
I'm not clear on something
If I run a big insert job of 100,000 records
INSERT into TableB
select * from TableA
But while it's running in a query window, I press cancel.
It says "Canceling Query", then "query canceled"
Will it rollback the insert ?
I tried it in a test case, and none were inserted, but not sure if that's a predictable result.
February 4, 2010 at 2:20 pm
Yes - it will roll back the transaction that was in process when you cancelled the insert.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 4, 2010 at 6:24 pm
If you are unsure of the result you could always wrap the statement in a transaction. That would ensure that if it was cancelled it would be rolled back.
Joie Andrew
"Since 1982"
February 4, 2010 at 6:51 pm
Yes, as Jeffrey mentioned, it would rollback the insert. Rollback would also take some time depending on the # of records it has processed..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 4, 2010 at 7:01 pm
And with the rollback, you must wait it out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 10:09 pm
But the log file will keep grow to accomadate both the insert and the delete.
Regards
Akhil
February 4, 2010 at 10:21 pm
Yes Sql Server rollback all the process.
Regards
Irshad Vaza
February 4, 2010 at 10:36 pm
You can cancell. But depending on batch size of insert the rol lback take time.
February 4, 2010 at 11:51 pm
Joie Andrew (2/4/2010)
If you are unsure of the result you could always wrap the statement in a transaction. That would ensure that if it was cancelled it would be rolled back.
No need. An insert is always in a transaction, regardless of whether one is explicitly created or not and SQL will always roll back an uncompleted transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply