Canceling an INSERT statement

Question

Canceling an INSERT statement

homebrew01

SSC Guru

Points: 55544
More actions
February 4, 2010 at 1:17 pm

#229664

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.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeffrey Williams SSC Guru Points: 90272 More actions · Answer 1

Yes - it will roll back the transaction that was in process when you cancelled the insert.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

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"

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

Yes, as Jeffrey mentioned, it would rollback the insert. Rollback would also take some time depending on the # of records it has processed..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

SQLbee Ten Centuries Points: 1009 More actions · Answer 5

But the log file will keep grow to accomadate both the insert and the delete.

Regards

Akhil

Irshad Vaza SSC Rookie Points: 46 More actions · Answer 6

Yes Sql Server rollback all the process.

Regards

Irshad Vaza

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 7

You can cancell. But depending on batch size of insert the rol lback take time.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass