将历史记录移到历史表中

现在表中的数据量越来越大，需要将历史记录移到历史表中。以下过程可以实现此功能。如果觉得有问题请联系我。

--用一配制表存需要转移数据的大表名称

--每次都查表名与tab_name相匹配且is_used=1的　说明是当前的历史表
create table config_table(configID int,             --ID
                          tab_name varchar(64),     --需要转移数据大表名称
                          condition_col varchar(32),--需要移动时间列名称
                          history_name varchar(64), --历史表名称
                          is_used int               --是否正在使用
                          )
                      --   1           test                 test_history             1

                     

create procedure sp_moveHistoryData(@tab_name varchar(32),@move_time varchar(12))                      
as
--sql
declare @sql           varchar(4000)
--column_name
declare @col_name      varchar(512)
--history column_name
declare @hiscol_name   varchar(512)
--history table_name
declare @histab_name   varchar(32)
declare @condition_col varchar(32)
declare @count         int
begin

select @histab_name = history_name,@condition_col=condition_col from config_table where tab_name=@tab_name and is_used=1

--if there is not history_name then return
if @histab_name is null or @histab_name=''
   return
--select source_table column into @col_name
set @sql = N'SELECT @sc_name = LEFT(colList,LEN(colList)-5)' +
' FROM ('+
' SELECT object_id,'+
' (SELECT replicate('' '',30-LEN( [name]))+[name]+'','' FROM sys.columns WHERE object_id=A.object_id  FOR XML PATH('''')) AS colList'+
' FROM sys.columns A where object_id=object_id('''+@tab_name+''')'+
' GROUP BY object_id'+
' ) b'
exec sp_executeSql @sql,N'@sc_name varchar(512) output',@sc_name=@col_name output

select @count=COUNT(1) from sys.objects where object_id=object_id(@histab_name)
-- if there is not exists history_table then crate and insert data to history_table
-- delete   from   tab_name when match  the  condition
if @count is null or @count ='' or @count =0
   begin
     set @sql = 'select '+@col_name +' into '+@tab_name+'_his1'+' from '+@tab_name+' where '+@condition_col+' <= convert(datetime,'+@move_time+' 23:59:59 )'     
     exec (@sql)
     set @sql = 'delete from '+@tab_name +' where '+@condition_col+' <= convert(datetime,'+@move_time+' 23:59:59 )'
     exec (@sql)    
   end
else
   begin
    --select source_table column into @hiscol_name
    set @sql = N'SELECT @hc_name = LEFT(colList,LEN(colList)-5)' +
    ' FROM ('+
    ' SELECT object_id,'+
    ' (SELECT replicate('' '',30-LEN( [name]))+[name]+'','' FROM sys.columns WHERE object_id=A.object_id  FOR XML PATH('''')) AS colList'+
    ' FROM sys.columns A where object_id=object_id('''+@histab_name+''')'+
    ' GROUP BY object_id'+
    ' ) b'
    exec sp_executeSql @sql,N'@sc_name varchar(512) output',@hc_name=@hiscol_name output
    
    if @col_name = @hiscol_name
    begin
        set @sql = 'insert into '+@histab_name+' select '+@col_name +' from '+@tab_name+' where '+@condition_col+' <= convert(datetime,'+@move_time+' 23:59:59 )'     
        exec (@sql)
        set @sql = 'delete from '+@tab_name +' where '+@condition_col+' <= convert(datetime,'+@move_time+' 23:59:59 )'
        exec (@sql)
    end
    else
    begin
        -- get historytable_name 's last char set add one  then rename histab_name
        set @count = cast(RIGHT(@histab_name,1) as int)
        set @count = @count+1
        set @histab_name = SUBSTRING(@histab_name,1,len(@histab_name)-1)+CAST(@count as varchar(1))
        
        set @sql = 'select '+@col_name +' into '+@tab_name+'_his1'+' from '+@tab_name+' where '+@condition_col+' <= convert(datetime,'+@move_time+' 23:59:59 )'     
        exec (@sql)
        set @sql = 'delete from '+@tab_name +' where '+@condition_col+' <= convert(datetime,'+@move_time+' 23:59:59 )'
        exec (@sql)    
        select @count = COUNT(1) from config_table
        set @count = @count+1
        update config_table set is_used = 0 where tab_name=@tab_name
        insert into config_table(configID,tab_name,condition_col, history_name,is_used )
              values(@count,@tab_name,@condition_col,@histab_name,1)
    end
   end
end