create procedure sp_test
as
begin
declare @s,varchar(8000),@v char(6)
--创建下个月的新表
select @v = convert(char(6),dateadd(mm,1,getdate()),112)
set @s = 'create table adinfo'+@v+'(......)'
exec(@s)
--删除三个月前的历史表
select @v = convert(char(6),dateadd(mm,-3,getdate()),112)
set @s = 'if exists(select 1 from sysobjects where id=object_id(''adinfo'+@v+'''))'
set @s = @s + ' drop table adinfo'+@v
exec(@s)
end
go
as
begin
declare @s,varchar(8000),@v char(6)
--创建下个月的新表
select @v = convert(char(6),dateadd(mm,1,getdate()),112)
set @s = 'create table adinfo'+@v+'(......)'
exec(@s)
--删除三个月前的历史表
select @v = convert(char(6),dateadd(mm,-3,getdate()),112)
set @s = 'if exists(select 1 from sysobjects where id=object_id(''adinfo'+@v+'''))'
set @s = @s + ' drop table adinfo'+@v
exec(@s)
end
go
as
begin
declare @sql varchar(8000),@nowmonth char(6),@agomonth char(6)
--创建下个月的新表
select @nowmonth = convert(char(6),getdate(),112)
set @sql = 'select * into adinfo'+@nowmonth+' from adinfo '
set @sql = @sql + ' delete from adinfo '
exec(@sql)
--删除三个月前的历史表
select @agomonth = convert(char(6),dateadd(mm,-3,getdate()),112)
set @sql = 'if exists(select 1 from sysobjects where id=object_id(''adinfo'+@agomonth+'''))'
set @sql = @sql + ' drop table adinfo'+@agomonth
exec(@sql)
end