具有事务和表变量的批处理存储过程

Question

这个周末我有一个数据迁移，后来发现我无法获得DBA的帮助，所以我的SQL Server知识缺乏。 所有其他DBA都是Oracle，不会接触SQL Server。 我在安全性方面受到限制，无法创建作业或 SSIS 包来处理此问题。

我

有一个脚本，我在其中作为批处理运行。 在此批处理中，我正在运行带有逻辑的存储过程。 存储过程有表变量，我刚刚读到您无法将它们作为事务运行。 有人可以关注这种整体方法，看看我是否遗漏了什么，或者我可以更有效地运行它吗？ BigTable 有大约 25M 条记录，所有索引、FK、限制都被丢弃。 我计划暂时为这批添加一些索引。 它将运行大约 5 天。

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

这是在不终止系统的情况下高效大容量复制表的正确方法。 仅当表在复制期间为只读时，此策略才有效。 如果您的表可以更改，则必须将其与跟踪和更新更改记录的另一种策略配对。

批处理复制方法将防止您在表上保持 4 天锁定，并允许您继续定期备份事务日志。 它还将防止在您需要停止它或失败时进行 4 天的回滚。

在发布之前运行此操作，然后在发布到表上限期间再次运行。 与往常一样，在尝试使用实时系统之前练习运行和停止脚本。

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