定期移植数据的存储过程代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】定期移植数据的存储过程

作者:Colin / 发布于2015/8/25/ 1253


	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'

试试其它关键字

　移植数据　

同语言下

可能有用的

Colin贡献的其它代码(7)