java历史数据迁移sql_迁移数据到历史表SQL .

--row batch: 100--row Process limit: 50000--data keep days: 90--*/

CREATE PROCEDUREdbo.TransferNInvoiceToHistoryBeforeDay@FromDate char(10)AS

SET NOCOUNT ON;--current trancount

DECLARE

@__trancount int;SELECT

@__trancount = @@TRANCOUNT;BEGINTRYDECLARE

@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) WITHNOWAIT--===========================================

--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;EXECsys.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

ENDTRYBEGINCATCHIF 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);ENDCATCHGO