迁移数据到历史表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