hi
i've got a piece of code below which works fine, i've since added in lines to try and catch any errors, using begin transaction, rollback etc.
There are 2 bulk inserts which take place, if either one fails I want to rollback completely, i.e. don't truncate the table, don't drop/re-create the indexes - don't do anything !
if the drop indexes fails I'm happy for the scipt to continue. Does my code look ok or have i over/under complicated ? (I don't think i need to rollback if the drop indexes fails)
thanks
kim
i've got a piece of code below which works fine, i've since added in lines to try and catch any errors, using begin transaction, rollback etc.
There are 2 bulk inserts which take place, if either one fails I want to rollback completely, i.e. don't truncate the table, don't drop/re-create the indexes - don't do anything !
if the drop indexes fails I'm happy for the scipt to continue. Does my code look ok or have i over/under complicated ? (I don't think i need to rollback if the drop indexes fails)
thanks
kim
Code:
/*
SQL CRM_load_LLPG.sql
Date Written 11 October 2005
Purpose To populate CAD table cad_base_table with combined LLPG data Amendments
*/
use crmcadt
go
BEGIN TRANSACTION
truncate table crmuser.cad_base_table
go
drop index crmuser.CAD_BASE_TABLE.IDX_CAD_BLDGNUMBR,
crmuser.CAD_BASE_TABLE.IDX_CAD_SUBBLDNAME,
crmuser.CAD_BASE_TABLE.IDX_CAD_BLDGNAME,
crmuser.CAD_BASE_TABLE.IDX_CAD_THOROFARE,
crmuser.CAD_BASE_TABLE.IDX_CAD_DTHOROFARE,
crmuser.CAD_BASE_TABLE.IDX_CAD_POSTCODE,
crmuser.CAD_BASE_TABLE.IDX_CAD_OLDPOSTCODE,
crmuser.CAD_BASE_TABLE.IDX_CAD_PAON,
crmuser.CAD_BASE_TABLE.IDX_CAD_SAON
go
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in droping indexes.', 16, 1)
RETURN
END
BULK INSERT crmcadt.crmuser.cad_base_table
FROM '\\serverA\E$\LLPG\llpg1.txt'
WITH
(DATAFILETYPE = 'char',
FIELDTERMINATOR='|')
go
BULK INSERT crmcadt.DBO.cad_base_table
FROM '\\servera\E$\LLPG\llpg2.txt'
WITH
(DATAFILETYPE = 'char',
FIELDTERMINATOR='|')
go
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting llpg data.', 16, 1)
RETURN
END
CREATE INDEX [IDX_CAD_BLDGNUMBR] ON [crmuser].[CAD_BASE_TABLE]([BLDGNUMBR]) ON [USER]
GO
CREATE INDEX [IDX_CAD_SUBBLDNAME] ON [crmuser].[CAD_BASE_TABLE]([SUBBLDNAME]) ON [USER]
GO
CREATE INDEX [IDX_CAD_BLDGNAME] ON [crmuser].[CAD_BASE_TABLE]([BLDGNAME]) ON [USER]
GO
CREATE INDEX [IDX_CAD_THOROFARE] ON [crmuser].[CAD_BASE_TABLE]([THOROFARE]) ON [USER]
GO
CREATE INDEX [IDX_CAD_DTHOROFARE] ON [crmuser].[CAD_BASE_TABLE]([DTHOROFARE]) ON [USER]
GO
CREATE INDEX [IDX_CAD_POSTCODE] ON [crmuser].[CAD_BASE_TABLE]([POSTCODE]) ON [USER]
GO
CREATE INDEX [IDX_CAD_OLDPOSTCODE] ON [crmuser].[CAD_BASE_TABLE]([OLDPOSTCODE]) ON [USER]
GO
CREATE INDEX [IDX_CAD_PAON] ON [crmuser].[CAD_BASE_TABLE]([PAON]) ON [USER]
GO
CREATE INDEX [IDX_CAD_SAON] ON [crmuser].[CAD_BASE_TABLE]([SAON]) ON [USER]
GO
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in re-creating indexes.', 16, 1)
RETURN
END
COMMIT