create proc test AS declare @name varchar(09),@sql varchar(1000) select @name='tab'+convert(char(06),getdate(),112) set @sql='if exists (select 1 from sysobjects where xtype=''u'' and [name]='''+@name+''') drop table '+@name+ ' select * into '+@name+' from a truncate table a' exec(@sql)GO
建立作业每月一号一点定时执行存储过程PU_A~~~CREATE TABLE A(NAME NCHAR(10),ID INT) INSERT A SELECT '李四',9 SELECT * FROM A GOCREATE PROC PU_A AS BEGINDECLARE @YEAR_MONTH NCHAR(9) SELECT @YEAR_MONTH='TAB'+CONVERT(NCHAR(6),DATEADD(MONTH,-1,GETDATE()),112) --SELECT @YEAR_MONTHEXEC SP_RENAME 'A',@YEAR_MONTHCREATE TABLE A(NAME NCHAR(10),ID INT)END GOEXEC PU_A GOSELECT * FROM A SELECT * FROM TAB200702 GODROP PROC PU_A DROP TABLE TAB200702 DROP TABLE A
AS
declare @name varchar(09),@sql varchar(1000)
select @name='tab'+convert(char(06),getdate(),112)
set @sql='if exists (select 1 from sysobjects where xtype=''u'' and [name]='''+@name+''') drop table '+@name+ ' select * into '+@name+' from a truncate table a'
exec(@sql)GO
INSERT A SELECT '李四',9
SELECT * FROM A
GOCREATE PROC PU_A
AS
BEGINDECLARE @YEAR_MONTH NCHAR(9)
SELECT @YEAR_MONTH='TAB'+CONVERT(NCHAR(6),DATEADD(MONTH,-1,GETDATE()),112)
--SELECT @YEAR_MONTHEXEC SP_RENAME 'A',@YEAR_MONTHCREATE TABLE A(NAME NCHAR(10),ID INT)END
GOEXEC PU_A
GOSELECT * FROM A
SELECT * FROM TAB200702
GODROP PROC PU_A
DROP TABLE TAB200702
DROP TABLE A