# 以SQL語法分批搬移大量資料

用SQL語法，為特定條件下的資料進行批次處理。
避免過量的transaction產生。

DECLARE @amt INT = 0;

WHILE EXISTS ( SELECT TOP 1 1
                 FROM RequestLog
                WHERE LogTime >= '2021-01-01'
                      AND LogTime < '2022-01-01'
             ) 
BEGIN
    -- 開始transaction控制
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 進行搬移
        INSERT INTO Log2021.dbo.RequestLog
        SELECT TOP 50000 *
          FROM RequestLog
         WHERE LogTime >= '2021-01-01'
               AND LogTime < '2022-01-01'
        ORDER BY LogTime;
        -- 進行刪除
        WITH T
             AS (SELECT TOP 50000 *
                   FROM RequestLog
                  WHERE LogTime >= '2021-01-01'
                        AND LogTime < '2022-01-01'
                 ORDER BY LogTime)
             DELETE T;
        COMMIT TRANSACTION;
        set @amt = @amt + 50000;
        print(getdate());
        -- print 已處理筆數
        print(@amt);
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT ERROR_MESSAGE();
        PRINT ERROR_PROCEDURE();
        PRINT ERROR_LINE();
    END CATCH
END