sql定期移植数据的存储过程

create PROCEDURE [dbo].[Pro_ZT_SYS_LogInfo_clear]
  
@dt_end datetime --清理此日期之前的数据
AS
BEGIN
SET NOCOUNT ON;
declare @sqlstr varchar(8000)
declare @table_name varchar(30)
declare @theday datetime
set @theday = dateadd(month,-1,@dt_end)
set @table_name = 'ZT_SYS_LogInfo_'+ltrim(year(@theday-1))+substring(ltrim(month(@theday-1)+100),2,2)
set @sqlstr = '
CREATE TABLE [dbo].'+@table_name+'(
		[LogID] [int] IDENTITY(1,1) NOT NULL,
		[LogType] [varchar](50) NULL,
		[OperatorType] [varchar](50) NULL,
		[FuncName] [varchar](50) NULL,
		[OperatorInfo] [text] NULL,
		[LoginIP] [varchar](50) NULL,
		[CreateBy] [varchar](50) NULL,
		[CreateDate] [datetime] NULL,
		[LastUpdateBy] [varchar](50) NULL,
		[LastUpdateDate] [datetime] NULL,
		[Remark] [text] NULL,
	 CONSTRAINT [PK_'+@table_name+'] PRIMARY KEY NONCLUSTERED 
	(
		[LogID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
'
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table_name) AND type in (N'U'))
   
exec(@sqlstr)

set @sqlstr = '
delete  from ZT_SYS_LogInfo
output deleted.*
into  '+@table_name+' ([LogID]
,[LogType]
,[OperatorType]
,[FuncName]
,[OperatorInfo]
,[LoginIP]
,[CreateBy]
,[CreateDate]
,[LastUpdateBy]
,[LastUpdateDate]
,[Remark])
where [CreateDate] < '''+convert(varchar(20),@theday)+''''

exec(@sqlstr)
END

 执行 exec  dbo.pro_ZT_SYS_LogInfo_clear @dt_end='2015-08-1 00:00'

转载于:https://www.cnblogs.com/xuxiaoshuan/p/4632975.html