SQL Server 存储过程，定时将临时表中今天之前的数据转存到对应年月的历史表中，执行如下语句创建存储过程，然后创建SQL Server 代理作业，计划设置为每天定时执行
USE [MYDATABASE]
GO
/****** Object: StoredProcedure [dbo].[sp_YourProcedure] Script Date: /10/31 17:06:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[sp_YourProcedure] --创建存储过程
as
BEGIN
BEGIN TRAN Tran_MoveData --开始事务
DECLARE @tran_error int;
declare @tablename varchar(50) ; --历史表名
declare @tableDate varchar(50); --历史表对应的年月
declare @sql NVARCHAR(MAX) ,@sql1 NVARCHAR(MAX);
declare @beginTime varchar(50),@endTime varchar(50);
set @beginTime = convert(datetime,convert(varchar(7),getdate()-1,120)+'-01',120);--昨天月初零点
set @endTime =convert(datetime,convert(varchar(10),getdate(),120));--今天零点
set @tableDate = CONVERT(varchar(7), GETDATE()-1, 120);--获取昨天的年月
set @tablename = 'T_BF_'+ REPLACE(REPLACE(REPLACE(@tableDate,'-',''),' ',''),':',''); --历史表名定义为前缀加时间
set @sql = 'insert into '+@tablename+' select ID
,Filed1
,Filed2
,Filed3
,Create_Time
from T_BF_TempTable where Create_Time between'''+@beginTime+''' and '''+@endTime+''''; --转存数据
set @sql1 = ' delete from T_BF_TempTable where Create_Time between'''+@beginTime+''' and '''+@endTime+''''; --删除原数据
SET @tran_error = 0;
BEGIN TRY
exec(@sql+@sql1)
END TRY
BEGIN CATCH
SET @tran_error = @tran_error + 1
END CATCH
IF(@tran_error > 0)
BEGIN
ROLLBACK TRAN;--回滚
END
ELSE
BEGIN
COMMIT TRAN;--提交
END
end
