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

代码语言

代码分类

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

作者:依依 / 发布于2017/3/16/ 600

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'

试试其它关键字

同语言下

可能有用的

依依贡献的其它代码(32)