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