using rollback in my insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using rollback in my insert

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-02-16 : 10:35:40

I want to use a rollback in my statement if trans fails for any reason and I want to make sure I do this correctly. Any help appreciated. thanks



DECLARE @affiliateIDTemp int
declare @affiliateID int

DECLARE r1 CURSOR FOR
SELECT affiliateIDTemp FROM tblTempsaleImport 

OPEN r1

FETCH NEXT FROM r1
INTO @affiliateIDTemp

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO tblaffiliates
(
columns1,
columns2
)
select
columns1,
columns2
from dbo.tblTempsaleImport
where affiliateIDTemp = @affiliateIDTemp


SELECT @affiliateID = SCOPE_IDENTITY()

INSERT INTO tblsaleExtendedData
(
columns1,
columns2,
columns3
) 
select
@affiliateID,
columns1,
columns2
from dbo.tblTempsaleImport
where affiliateIDTemp = @affiliateIDTemp

INSERT INTO tblaffiliatesSubID

 (
columns1,
columns2
 )
SELECT
 
 @affiliateID,
columns2
from dbo.tblTempsaleImport
WHERE affiliateIDTemp = @affiliateIDTemp


FETCH NEXT FROM r1
INTO @affiliateIDTemp 
END

CLOSE r1
DEALLOCATE r1

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-16 : 11:01:17

Rollblack for whole batch, or only the current transfer?

E 12°55'05.63"
N 56°04'39.26"

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-02-16 : 11:04:31

whole tranasct, thanks

quote:
Originally posted by Peso

Rollblack for whole batch, or only the current transfer?

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-16 : 11:12:09

try this

BEGIN TRY

BEGIN TRANSACTION

DECLARE @affiliateIDTemp int
declare @affiliateID int

DECLARE r1 CURSOR FOR
SELECT affiliateIDTemp FROM tblTempsaleImport

OPEN r1

FETCH NEXT FROM r1
INTO @affiliateIDTemp

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO tblaffiliates
(
columns1,
columns2
)
select
columns1,
columns2
from dbo.tblTempsaleImport
where affiliateIDTemp = @affiliateIDTemp

SELECT @affiliateID = SCOPE_IDENTITY()

INSERT INTO tblsaleExtendedData
(
columns1,
columns2,
columns3
)
select
@affiliateID,
columns1,
columns2
from dbo.tblTempsaleImport
where affiliateIDTemp = @affiliateIDTemp

INSERT INTO tblaffiliatesSubID

(
columns1,
columns2
)
SELECT

@affiliateID,
columns2
from dbo.tblTempsaleImport
WHERE affiliateIDTemp = @affiliateIDTemp

FETCH NEXT FROM r1
INTO @affiliateIDTemp
END

CLOSE r1
DEALLOCATE r1

COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

SELECT ERROR_MESSAGE()

RETURN -1
END CATCH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources