Batch stored procedure with transactions and table variables

Question

I have a data migration this weekend and come to find out I wont be able to get a DBA's help so my SQL Server knowledge is lacking. All of the other DBA's are Oracle and wont touch SQL Server. I'm limited on security, and I can't create a job or SSIS package to handle this.

I have a script in which I'm running as a batch. Within this batch, I am running a stored procedure with the logic. The stored procedure has table variables, and I just read that you're unable to run these as a transaction. Can someone eyeball this overall approach and see if I'm missing anything or I can run it more efficiently? The BigTable has approximately 25M records and all indexes, FKs, contraints are dropped. I plan on temporarily adding a few indexes for this batch. It will run around 5 days.

Create Procedure ConvertStuff AS
BEGIN

declare @id uniqueIdentifier
declare @importdate DateTime
declare @Data varchar(max)

declare @tableX table 
    ---
declare @tableY table 
    ---
declare @tableZ table 
    ---


SET NOCOUNT ON
    select top 1 @ID = bt.ID, @Data = bt.RawData, @importDate = bt.ImportDate from Processed p with (NOLOCK)
        Inner join BigTable bt with (NOLOCK) on p.ID = bt.ID where p.isProcessed = 0

    while (not @ID is null)
    Begin
        BEGIN TRY
            --Do stuff here
        END TRY
        BEGIN CATCH

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage,
                       @ErrorSeverity,
                       @ErrorState
                       );


            update bigTable set isProcessed = -1 where ID = @ID
            break
        END CATCH
    select top 1 @ID = bt.ID, @Data = bt.RawData, @importDate = bt.ImportDate from Processed p with (NOLOCK)
        Inner join BigTable bt with (NOLOCK) on p.ID = bt.ID where p.isProcessed = 0
    END
    --Do I need to drop the @ tables here? Should I convert these to # ?
END

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Running this....

-- This will be dropped once the migration is done
CREATE TABLE [Processed]
(
    [ID] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [isProcessed] [bit] Default(0) NOT NULL,
)

CREATE NONCLUSTERED INDEX [idx_isProcessed] ON [Processed]
(
    [isProcessed] ASC
)

GO

SET ROWCOUNT 25000

declare @msg varchar(50)
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

While (1=1) 
BEGIN
    BEGIN TRY
    BEGIN TRANSACTION
    Insert into [Processed] (ID, isProcessed) 
        Select ID, 0 from BigTable where recordUpdated = 0

    exec ConvertStuff

    IF @@ROWCOUNT = 0
    BEGIN
        Print @@ROWCOUNT
        COMMIT TRANSACTION
        BREAK
    END

    COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION


        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
        BREAK
    END CATCH
END

drop table Processed

Answer 1

This is the proper way to efficiently bulk copy a table without killing your system. This strategy only works if the table is read-only during the copy. If your table can change you'll have to pair this with another strategy for tracking and updating changed records.

The batch copy approach will prevent you from holding a 4 day lock on the table and will allow you to continue backing up your transaction logs at regular intervals. It also will prevent a 4 day rollback in the event you either need to stop it or it fails.

Run this before your release, then again during the release to cap of the table. As always, practice running and stopping your script before trying on a live system.

DECLARE @CurrentId UNIQUEIDENTIFIER,
        @BatchSize INT;
SET @BatchSize = 50000;

SELECT TOP 1
    @CurrentId = ID
FROM NewTable
ORDER BY ID DESC;

SELECT
    @LastId = ID
FROM OldTable
ORDER BY ID DESC;

IF (@CurrentId IS NULL)
    SET @CurrentId = '00000000-0000-0000-0000-000000000000';

PRINT 'Copying from ' + CONVERT(VARCHAR(40), @CurrentId) + ' to ' + CONVERT(VARCHAR(40), @LastId);

CREATE TABLE #Batch
(
    ID UNIQUEIDENTIFIER
);

WHILE (@CurrentId < @LastId)
BEGIN
    PRINT CONVERT(VARCHAR(40), @CurrentId);

    TRUNCATE TABLE #Batch;

    -- Get your new batch
    INSERT INTO #Batch
    SELECT TOP (@BatchSize)
        *
    FROM OldTable
    WHERE ID > @CurrentId
    ORDER BY ID;

    -- I'd recommend being specific with columns, you might also need to turn on 'SET IDENTITY_INSERT <Table> ON'
    INSERT INTO NewTable
    SELECT *
    FROM OldTable
    INNER JOIN #Batch ON #Batch.ID = OldTable.ID
    LEFT JOIN NewTable ON NewTable.ID = OldTable.ID
    WHERE NewTable.ID IS NULL;

    IF (@@ERROR <> 0)
        BREAK

    SELECT TOP 1
        @CurrentId = ID
    FROM #Batch
    ORDER BY ID DESC;
END