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
BEGIN TRANSACTION MyName
While @LoopCount<@Count
BEGIN
	INSERT INTO TestTab values (1)
	SET @LoopCount=@LoopCount+1
	IF (@@error <> 0)
		BEGIN
			ROLLBACK TRANSACTION MyName
		END
END
ASKER CERTIFIED SOLUTION
brejk

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