January 30, 2004 at 11:39 am
Hello all, I have a batch/T-SQL script with multiple Bulk Insert statements. I have wrapped all these Bulk Insert statements with a Transaction statement. What I want is that when any of the Bulk Insert statements within the Transaction fails, all the Bulk Inserts should be Rolled Back but it's not doing that. How will I be able to do this (All or Nothing approch). Is this possible with the Bulk Insert utility or not. IF it is, how is it done. Here is a snippet of code for you understanding. Thanks
Code Example:
USE DatabaseName
GO
SET XACT_ABORT ON
BEGIN TRANSACTION
BULK INSERT dbo.TBL1
FROM '\\..\Data_BCP\TBL1.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
BULK INSERT dbo.TBL2
FROM '\\...\Data_BCP\TBL2.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
BULK INSERT dbo.TBL3
FROM '\\...\Data_BCP\TBL3.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
BULK INSERT dbo.TBL4
FROM '\\gallo\DAIDS\Data_BCP\TBL4.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
ROLLBACK TRANSACTION
GO
How can I get this to roll back all the Bulk Inserts. Help, Thanks
January 30, 2004 at 1:14 pm
What is your database recovery mode when performing Bulk insert?
January 30, 2004 at 2:28 pm
The recovery mode is full recovery. Should it be changed Bulk Logged?
January 30, 2004 at 5:23 pm
It has to be full recovery mode if you want to rollback evrything.
USE DatabaseName
GO
SET XACT_ABORT ON
go -- I added GO here and The setting of SET XACT_ABORT is set at execute or run time and not ------ at parse time according to BOL.
BEGIN TRANSACTION
BULK INSERT dbo.TBL1
FROM '\\..\Data_BCP\TBL1.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
BULK INSERT dbo.TBL2
FROM '\\...\Data_BCP\TBL2.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
BULK INSERT dbo.TBL3
FROM '\\...\Data_BCP\TBL3.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
BULK INSERT dbo.TBL4
FROM '\\gallo\DAIDS\Data_BCP\TBL4.txt'
WITH
(
DATAFILETYPE = 'native ',
KEEPNULLS,
MAXERRORS = 1,
)
ROLLBACK TRANSACTION
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply