迁移数据到历史表SQL(转)

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

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

 

[sql] view plain copy

    -- 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