Our company is looking for functionalilty/or automated way to archive data in SQL Server. Currently, we create archive tables with identity surrogate, and develop stored procedures to import data. We are conducting this for 100+ tables, data is 2 years or older. Does SQL Server have functionality or automated way to archive data (Wizard tool, UI) or does person have to develop stored procedures? I am thinking of creating dynamic sql to write script for 100+ tables.

Thanks,

-- If no functionality exists, will create dynamic sql for tables using template below

create table dbo.CustomerTransaction
(
    CustomerTransactionId bigint primary key identity(1,1) not null ,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null  
 )

 
create table dbo.CustomerTransactionArchive
(
    CustomerTransactionArchiveId bigint primary key identity(1,1) not null ,
    CustomerTransactionId bigint null,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null  
 )


create procedure dbo.CustomerTransactionArchive
as

    declare rowmin bigint = (Select min(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowmax bigint = (Select max(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowcounter bigint = rowcountermin


      -- insert into 100,000 record block, do not want to lock too many pages in table
    while rowcounter <= rowmax 
    begin
  
        insert into dbo.CustomerTransactionArchive  (
        select * from dbo.CustomerTransactionArchive  
        where CustomerTransactionId between rowcounter and (rowcounter +100000) 
        and Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))

        rowcounter = rowcounter + 100001
    end
       

Our company is looking for functionalilty/or automated way to archive data in SQL Server. Currently, we create archive tables with identity surrogate, and develop stored procedures to import data. We are conducting this for 100+ tables, data is 2 years or older. Does SQL Server have functionality or automated way to archive data (Wizard tool, UI) or does person have to develop stored procedures? I am thinking of creating dynamic sql to write script for 100+ tables.

Thanks,

-- If no functionality exists, will create dynamic sql for tables using template below

create table dbo.CustomerTransaction
(
    CustomerTransactionId bigint primary key identity(1,1) not null ,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null  
 )

 
create table dbo.CustomerTransactionArchive
(
    CustomerTransactionArchiveId bigint primary key identity(1,1) not null ,
    CustomerTransactionId bigint null,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null  
 )


create procedure dbo.CustomerTransactionArchive
as

    declare rowmin bigint = (Select min(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowmax bigint = (Select max(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowcounter bigint = rowcountermin


      -- insert into 100,000 record block, do not want to lock too many pages in table
    while rowcounter <= rowmax 
    begin
  
        insert into dbo.CustomerTransactionArchive  (
        select * from dbo.CustomerTransactionArchive  
        where CustomerTransactionId between rowcounter and (rowcounter +100000) 
        and Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))

        rowcounter = rowcounter + 100001
    end
       

Archive tables will be in different database