迁移数据到历史表SQL .

有时工作需要需要把当前表的数据，移到历史表中，而历史表基本是以时间（年）为后缀来命名历史表的，如 A_2011，A_2012，在移数据时，要按数据的时间，移到不同的表中，且由于如果数据有同步。一次处理的数据不能太大。否则同步链会被Block.所以需要批理处理。

下面是一个通用的写法，可以作为参考！（这个应该是出自邹建大侠之手，因为需要写这样的处理，去找了下类似代码，找到的）

-- row batch:                100
-- row Process limit:        50000
-- data keep days:            90
-- */
CREATE PROCEDURE dbo.TransferNInvoiceToHistoryBeforeDay
    @FromDate char(10)
AS
SET NOCOUNT ON;


-- current trancount
DECLARE
    @__trancount int;
SELECT
    @__trancount = @@TRANCOUNT;


BEGIN TRY
    DECLARE
        @row_batch int,
        @row_limit int,
        @row_process int,
        @row_count int,
        @date_begin datetime,
        @date_end datetime;


    -- row batch and data keep date
    SELECT
        @row_batch = 100,                                    -- each batch process rows
        @row_limit = 50000,                            -- total row process limit
        @date_end = @FromDate,--DATEDIFF(Day, 90, GETDATE()),    -- process top date
        @row_process = 0;                                                -- process rows total


    -- ===========================================
    -- get process begin date and rows
    SELECT
        @date_begin = MIN(InvoiceDate),
        @row_count = COUNT(*)
    FROM 需要处理的当前表名   WITH(NOLOCK)
    WHERE InvoiceDate < @date_end;


    IF @row_count = 0        
        RETURN;
    ELSE IF @date_begin IS NULL
    BEGIN
        RAISERROR(N'column InvoiceDate include NULL value, please fix it', 16, 1)
    END
    
    IF @row_limit IS NULL OR @row_limit <= 0
        SET @row_limit = @row_count;


    RAISERROR('%d rows need process, current process limit %d rows', 10, 1, @row_count, @row_limit) WITH NOWAIT


    -- ===========================================
    -- process by year
    DECLARE
        @date datetime;
    SET @date = @date_begin;


    WHILE @row_process < @row_limit
            AND @date < @date_end
    BEGIN
        -- process date and sql
        DECLARE
            @sql nvarchar(4000),
            @_date_begin datetime,
            @_date_end datetime;


        SELECT
            @_date_begin = @date,
            @_date_end = CASE
                            WHEN DATEDIFF(Year, @_date_begin, @date_end) = 0 THEN @date_end
                            ELSE DATEADD(Year, YEAR(@_date_begin) - 1899, 0)
                        END,
            @date = @_date_end,
            @row_count = @row_batch,
            @sql = N'
DECLARE @tb_id TABLE(
    invoiceNumber int
        PRIMARY KEY
);
INSERT @tb_id
SELECT TOP(@row_batch)
    invoiceNumber
FROM Nact.dbo.NewEgg_InvoiceMaster A
WHERE InvoiceDate >= @_date_begin
    AND InvoiceDate < @_date_end;

DELETE A
OUTPUT deleted.*
    INTO 历史表名不带时间部份' + RTRIM(Year(@_date_begin)) + N'
FROM 当前表名 A,
    @tb_id B
WHERE A.invoiceNumber = B.invoiceNumber;
';


        -- ===========================================
        -- process by batch for year
        WHILE @row_process < @row_limit
                AND @row_count = @row_batch
        BEGIN
            -- move data
            IF @__trancount = 0
                BEGIN TRAN;
            ELSE
                SAVE TRAN __TRAN_SavePoint;


            EXEC sys.sp_executesql
                @sql,
                N'
                    @row_batch int,
                    @_date_begin datetime,
                    @_date_end datetime                    
                ',
                @row_batch, @_date_begin, @_date_end;


            SELECT
                @row_count = @@ROWCOUNT,
                @row_process = @row_process + @row_count;
        
            IF XACT_STATE() = 1 AND @__trancount = 0
                COMMIT;
        END
    END


    IF @__trancount = 0
    BEGIN
        IF XACT_STATE() = -1
            ROLLBACK TRAN;
        ELSE
        BEGIN        
            WHILE @@TRANCOUNT > 0
                COMMIT TRAN;
        END
    END
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
    BEGIN
        IF @__trancount = 0
            ROLLBACK TRAN;
        ELSE IF XACT_STATE() = 1 AND @@TRANCOUNT > @__trancount
            ROLLBACK TRAN __TRAN_SavePoint;
    END


    DECLARE
        @__error_number int,
        @__error_message nvarchar(2048),
        @__error_severity int,
        @__error_state int,
        @__error_line int,
        @__error_procedure nvarchar(126),
        @__user_name nvarchar(128),
        @__host_name nvarchar(128);


    SELECT
        @__error_number = ERROR_NUMBER(),
        @__error_message = ERROR_MESSAGE(),
        @__error_severity = ERROR_SEVERITY(),
        @__error_state = ERROR_STATE(),
        @__error_line = ERROR_LINE(),
        @__error_procedure = ERROR_PROCEDURE(),
        @__user_name = SUSER_SNAME(),
        @__host_name = HOST_NAME();


    RAISERROR(
        N'User: %s, Host: %s, Procedure: %s, Error %d, Level %d, State %d, Line %d, Message: %s ',
        @__error_severity,
        1,
        @__user_name,
        @__host_name,
        @__error_procedure,
        @__error_number,
        @__error_severity,
        @__error_state,
        @__error_line,
        @__error_message);
END CATCH




GO