troubleshooting Question

Roolback in SQl server 2005

Avatar of hazemfadl
hazemfadlFlag for Egypt asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
12 Comments1 Solution290 ViewsLast Modified:
I have a query that insert huge rows (30000 Rows), may be while inserting operation the power of computer off or any other reason that prevent operation complete so I want to do rollback (really I do roll back as in query but Can't do any operation on table like select so I have a message that says you have some uncommitted operation, so I want to do the following
1- Insert huge rows
2- If fails rollback inserted rows and return table to original state
3- I can do any operation on table (insert, delete, etc) after rollback
4- Rollback only this operation like if table has 10 records and I insert new 10 records and some fails occurred, rollback only that last 10 rows

Declare @LoopCount int
Declare @Count int
SET @LoopCount=0
SET @Count=300000
While @LoopCount<@Count
	INSERT INTO TestTab values (1)
	SET @LoopCount=@LoopCount+1
	IF (@@error <> 0)

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros