Canceling an INSERT statement

Question

Post reply

Canceling an INSERT statement

homebrew01

SSC Guru

Points: 55454
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: 89773 More actions · Answer 1

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

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: 281324 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: 1004494 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