EricALionsFan
Reputation: 57
Best way to move data from one table to another SQL Server
I have eight tables that I need to move data from one table to another for archive & data retention reasons. I am not the best at stored procedures so I am looking for ideas and best practices for moving data - even if it it means starting over from scratch. I'm sure I am doing something wrong but I am willing to learn.
My current T-SQL Code:
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
Upvotes: 0
Views: 1825
Answers (2)
Tom Suykens
Reputation: 11
Just do it in one go. Set the database in simple recovery mode, if possible; keep batches small to avoid growth of the logfile. This code will roll back completely in case of failure:
DELETE FROM OC_VNOTE
OUTPUT deleted.*
INTO OC_VNOTE_ARCHIVE
WHERE OC_VNOTE.DATETIME < @ARCHIVE_DATETIME_GS;
Upvotes: 1
SQLpro
Reputation: 5131
First you do not need to have all those TRY CATCH in your code. Only one will be sufficient with this code :
BEGIN CATCH
IF XACT_STATE()<> 0
ROLLBACK;
THROW;
END CATCH
Second, you can use partition to switch sets of rows from one table to another by a simple "SWITCH PARTITION ... " command that will act on all your table is they have the same partition parameter (which seems to be the case with the DTATIME column...
Third you can do a transaction on each couple of INSERT/DELETE to minimize the transaction log growth
Upvotes: 1
Related Questions
- Move SQL data from one table to another
- Transfer data from one table to another identical table
- How to store copy each row from one table into another table
- Transfer data from one table to another in sql server
- Stored Procedure to select columns and insert into different table
- Moving data from one table to another table on a different server
- Moving data over - need cleaner method
- procedure to take information from one table and write to another table
- Insert values from one table to another
- Unsure how to take data from one table and move it into another