将数据从一个表移动到另一个SQL Server的最佳方式

Question

我有八个表，我需要将数据从一个表移动到另一个表进行存档&数据保留原因。我不是最擅长存储过程的，所以我正在寻找移动数据的想法和最佳实践——即使这意味着从头开始。我知道我做错了什么，但我愿意学习。

我当前的T-SQL代码:

USE [SPCTST]
GO
/****** Object:  StoredProcedure [dbo].[sp_Insert_Data_Into_Archive_Tables]    Script Date: 5/10/2022 11:27:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/* =============================================
Author:        Eric Brenner
Create date:   3/3/2022
Description:   Moves data from the ACTIVE tables
to the ARCHIVE tables based on the 
ARCHIVE_DATETIME below.
Updates:       Added DELETE statement just after
inserting records into the ARCHIVE
tables.
5/9/2022:      Removed EXCEPT clause, added
seperate BEGIN and COMMIT
clauses, and TRY statements.
============================================= */
ALTER PROCEDURE [dbo].[sp_Insert_Data_Into_Archive_Tables]
--@Archive_Months tinyint -- Use this as a parameter if desired in the future. Currently disabled.
AS
-- @ARCHIVE_DATETIME is a variable to contain the number of months worth of data you want to keep in the ARCHIVE table(s).
DECLARE @ARCHIVE_DATETIME datetime = DATEADD(month, -26, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) -- a little over 2 years
-- Convert Date to a 16-digit string to use against because of the db design
DECLARE @ARCHIVE_DATETIME_GS nvarchar(16) = CONCAT(YEAR(@ARCHIVE_DATETIME), --year
FORMAT(@ARCHIVE_DATETIME,'MM'), --month
FORMAT(@ARCHIVE_DATETIME,'dd'), --day
'00000000')

-- If the temporary table 'DateTimeValues' exists, remove it
IF OBJECT_ID('TEMPDB..#DateTimeValues') IS NOT NULL
BEGIN
DROP TABLE #DateTimeValues
END

-- Insert data into the Archive Tables
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_VNOTE_ARCHIVE
SELECT * FROM OC_VNOTE WHERE OC_VNOTE.DATETIME < @ARCHIVE_DATETIME_GS
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_VNOTE WHERE OC_VNOTE.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_VMON_AUX_ARCHIVE
SELECT * FROM OC_VMON_AUX WHERE OC_VMON_AUX.DATETIMEAUX < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_VMON_AUX WHERE OC_VMON_AUX.DATETIMEAUX < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_VMON_ARCHIVE
SELECT * FROM OC_VMON WHERE OC_VMON.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_VMON WHERE OC_VMON.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_VDAT_AUX_ARCHIVE
SELECT * FROM OC_VDAT_AUX WHERE OC_VDAT_AUX.DATETIMEAUX < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_VDAT_AUX WHERE OC_VDAT_AUX.DATETIMEAUX < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_VDATA_ARCHIVE
SELECT * FROM OC_VDATA WHERE OC_VDATA.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_VDATA WHERE OC_VDATA.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
--------------------------------------------------------------------------------------------------------------------
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_DNOTE_ARCHIVE_PC
SELECT * FROM OC_DNOTE_PC WHERE OC_DNOTE_PC.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_DNOTE_PC WHERE OC_DNOTE_PC.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_DDAT_AUX_ARCHIVE_PC
SELECT * FROM OC_DDAT_AUX_PC WHERE OC_DDAT_AUX_PC.DATETIMEAUX < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_DDAT_AUX_PC WHERE OC_DDAT_AUX_PC.DATETIMEAUX < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO OC_DDATA_ARCHIVE_PC
SELECT * FROM OC_DDATA_PC WHERE OC_DDATA_PC.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM OC_DDATA_PC WHERE OC_DDATA_PC.DATETIME < @ARCHIVE_DATETIME_GS;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
GO

Answer 1

First你不需要在你的代码中使用所有的TRY CATCH。在这个代码中，只有一个是足够的:

BEGIN CATCH
IF XACT_STATE()<> 0
ROLLBACK;
THROW;
END CATCH 

第二，您可以通过简单的" switch partition…使用分区将行集从一个表切换到另一个表。";命令将对所有表起作用，如果它们具有相同的分区参数(DTATIME列似乎就是这种情况…

3您可以在每一对INSERT/DELETE上执行一个事务，以最小化事务日志的增长

Answer 2

一次性完成。如果可能，将数据库设置为简单恢复模式;保持批处理较小，以避免日志文件的增长。以下代码将在失败的情况下完全回滚:

DELETE FROM OC_VNOTE 
OUTPUT deleted.*
INTO OC_VNOTE_ARCHIVE
WHERE OC_VNOTE.DATETIME < @ARCHIVE_DATETIME_GS;