Re: Copy data from flat table into multiple tables

From: Aaron Prohaska (REMOVE_THISmohaaron_at_gmail.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 16:53:35 -0700

Nigel Rivett wrote:
> I won't go into ways of making this simpler or faster (dts not being one of
> them)
> Your problem is that @@error is set (and reset) by every sql statement so
> you need to check it after every statement and if it's non zero then roll
> back the transaction and exit the loop.
> begin loop
> e.g.
> insert
> if @@error <> 0 goto fail
> insert
> if @@error <> 0 goto fail
> insert
> if @@error <> 0 goto fail
> end loop
>
> commit tran
> return
> fail:
> rollback ktran
>
>
>
> "Aaron Prohaska" wrote:
>
>
>>The script below does most of what I need except that I'm having a few
>>problems with it. What I'm trying to do is copy a row at a time from an
>>old table into five new tables. The only way I was able to do this was
>>to select the fields I need from the old table and then loop through
>>each row in the resultset using a curser and insert the fields into the
>>appropriate five tables.
>>
>>This part all works fine and for the size of the reslutset doesn't take
>>too long. The problem that I'm having is that the script doesn't make
>>the insert as a single transaction, so if the script gets half way
>>through the resultset and there is an error the tables won't have
>>consistent data.
>>
>>I had some suggestions to use a dts package(s), but I have not been able
>>to figure out how to create a package that will do what I need.
>>
>>Does anyone know how I might solve this problem?
>>
>>BEGIN TRANSACTION
>>
>>DECLARE
>>@ULogin varchar(50),
>>@UPassword varchar(50),
>>@UFirstName varchar(25),
>>@UMiddleName varchar(25),
>>@ULastName varchar(25),
>>@UCreateDate datetime,
>>@UUpdateDate datetime,
>>@UHeight decimal (5,2),
>>@UMeas3 decimal (5,2),
>>@UMeas4 decimal (5,2),
>>@UMeas2 decimal (5,2),
>>@UMeas1 decimal (5,2),
>>@UFlex tinyint,
>>@UWeight decimal (5,2),
>>@UShoeSize decimal (5,2),
>>@NewUserID int,
>>@IsError bit
>>
>>SET @IsError = 0
>>
>>DECLARE csrUsers CURSOR STATIC LOCAL FORWARD_ONLY FOR
>> SELECT
>> UID, UFirstName, UMiddleName, ULastName, UCreateDate,
>>UUpdateDate, --// Data For Users And Customers Table
>> ULogin, UPassword, --// Data For UserLogin Table
>> UHeight, UMeas3, UMeas4, UMeas2, UMeas1, UFlex, UWeight,
>>UShoeSize --//
>> FROM
>> [WS1].[dbo].[Users]
>> WHERE
>> (ULogin IS NOT NULL AND ULogin <> '')
>> AND
>> (UPassword IS NOT NULL AND UPassword <> '')
>> AND
>> (UFirstName IS NOT NULL AND UFirstName <> '')
>> AND
>> (ULastName IS NOT NULL AND ULastName <> '')
>>
>>open csrUsers
>>
>>FETCH NEXT
>>FROM
>> csrUsers
>>INTO
>> --// Data For Users And Customers Table
>> @UFirstName, @UMiddleName, @ULastName, @UCreateDate, @UUpdateDate,
>> --// Data For UserLogin Table
>> @ULogin, @UPassword,
>> --// Data For UserFit Table
>> @UHeight, @UMeas3, @UMeas4, @UMeas2, @UMeas1, @UFlex, @UWeight,
>>@UShoeSize
>>
>>WHILE @@FETCH_STATUS = 0
>>BEGIN
>> --// Insert Into Users Table
>> INSERT INTO [WS2].[dbo].[Users]
>> ([FirstName], [MiddleName], [LastName], [CreateDate], [UpdateDate])
>> VALUES
>> (@UFirstName, @UMiddleName, @ULastName, @UCreateDate, @UUpdateDate)
>>
>> SET @NewUserID = SCOPE_IDENTITY()
>>
>> --// Insert Into Customers Table
>> INSERT INTO [WS2].[dbo].[Customers]
>> ([CustomerID])
>> VALUES
>> (@NewUserID)
>>
>> --// Insert Into UserLogin
>> INSERT INTO [WS2].[dbo].[UserLogin]
>> ([UserID], [Username], [Password])
>> VALUES
>> (@NewUserID, @ULogin, @UPassword)
>>
>> --// Insert Into EmailAddresses
>> INSERT INTO [WS2].[dbo].[EmailAddresses]
>> ([IsPrimary], [UserID], [Email])
>> VALUES
>> (1, @NewUserID, @ULogin)
>>
>> IF (@UHeight IS NULL) BEGIN
>> SET @UHeight = 0
>> END
>> IF (@UMeas3 IS NULL) BEGIN
>> SET @UMeas3 = 0
>> END
>> IF (@UMeas4 IS NULL) BEGIN
>> SET @UMeas4 = 0
>> END
>> IF (@UMeas2 IS NULL) BEGIN
>> SET @UMeas2 = 0
>> END
>> IF (@UMeas1 IS NULL) BEGIN
>> SET @UMeas1 = 0
>> END
>> IF (@UFlex IS NULL) BEGIN
>> SET @UFlex = 0
>> END
>> IF (@UWeight IS NULL) BEGIN
>> SET @UWeight = 0
>> END
>> IF (@UShoeSize IS NULL) BEGIN
>> SET @UShoeSize = 0
>> END
>>
>> IF (@UHeight > 0 AND @UMeas3 > 0 AND @UMeas4 > 0 AND @UMeas2 > 0
>>AND @UMeas1 > 0 AND @UFlex > 0 AND @UWeight > 0 AND @UShoeSize > 0) BEGIN
>> --// Insert Into UserFit
>> INSERT INTO [WS2].[dbo].[UserFit]
>> ([UserID], [Height], [SternumNotch], [Inseam], [ArmLength],
>>[ShoulderWidth], [Flexibility], [Weight], [ShoeSize])
>> VALUES
>> (@NewUserID, @UHeight, @UMeas3, @UMeas4, @UMeas2, @UMeas1,
>>@UFlex, @UWeight, @UShoeSize)
>> END
>>
>> FETCH NEXT
>> FROM
>> csrUsers
>> INTO
>> --// Data For Users And Customers Table
>> @UFirstName, @UMiddleName, @ULastName, @UCreateDate, @UUpdateDate,
>> --// Data For UserLogin Table
>> @ULogin, @UPassword,
>> --// Data For UserFit Table
>> @UHeight, @UMeas3, @UMeas4, @UMeas2, @UMeas1, @UFlex, @UWeight,
>>@UShoeSize
>>END
>>
>>CLOSE csrUsers
>>DEALLOCATE csrUsers
>>
>>IF( @@ERROR <> 0 ) SET @IsError = 1
>>
>>IF( @IsError = 0 )
>> COMMIT TRANSACTION
>>ELSE
>> ROLLBACK TRANSACTION
>>GO
>>
>>thanks,
>>
>>Aaron
>>

Nigel,

Thank you! I have not got the script working the way I wanted.

Aaron

Quantcast