DECLARE @YEAR INT,@SQL VARCHAR(MAX) SET @YEAR=2010 SELECT @SQL=ISNULL(@SQL,'')+'CREATE TABLE A_'+RIGHT(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01'),112),4)+'(COL1 INT);' FROM MASTER..SPT_VALUES WHERE TYPE='P' AND DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01') < LTRIM(@YEAR+1)+'-01-01' EXEC (@SQL)
DECLARE @YEAR INT,@SQL VARCHAR(MAX),@TB_FIRST_NAME VARCHAR(100),@COLS VARCHAR(8000) SET @YEAR=2010 SET @TB_FIRST_NAME='A_' SET @COLS='COL1 INT ,COL2 VARCHAR(50)' SELECT @SQL=ISNULL(@SQL,'') +'CREATE TABLE ' +@TB_FIRST_NAME +RIGHT(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01'),112),4) +'('+@COLS+');' FROM MASTER..SPT_VALUES WHERE TYPE='P' AND DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01') < LTRIM(@YEAR+1)+'-01-01' EXEC (@SQL)SELECT NAME FROM SYS.TABLES WHERE NAME LIKE 'A_%' --365行
--建全年的日期表 select distinct convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number)) from master..spt_values a, master..spt_values b where a.number between 1 and 12 and a.type='p' and b.number between 1 and 31 and a.type='p' and isdate(ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))=1 order by convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))
如果@name 长度不够的话,可以通过游标,取一个日期,创建一个表 declare @name varchar(max),@year intselect @year=2010select distinct convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number)) as name into rqb ---放到日期表 from master..spt_values a, master..spt_values b where a.number between 1 and 2 and a.type='p' --月份以 between 1 and 2 为例,你改成1到12 and b.number between 1 and 3 and a.type='p' --日期以 between 1 and 3 为例,你改成1到31 and isdate(ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))=1 order by convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))select @name=isnull(@name,' create table tbl_smresult_')+ltrim(month(name))+ltrim(day(name))+'(id int); create table tbl_smresult_' from rqb set @name=left(@name,len(@name)-26)exec(@name) drop table rqb
我就服了,我的代码好像总是被无视 DECLARE @YEAR INT,@SQL VARCHAR(MAX),@TB_FIRST_NAME VARCHAR(100),@COLS VARCHAR(8000) SET @YEAR=2008 SET @TB_FIRST_NAME='A_' SET @COLS='COL1 INT ,COL2 VARCHAR(50)' SELECT @SQL=ISNULL(@SQL,'') +'CREATE TABLE ' +@TB_FIRST_NAME +RIGHT(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01'),112),4) +'('+@COLS+');' FROM MASTER..SPT_VALUES WHERE TYPE='P' AND DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01') < LTRIM(@YEAR+1)+'-01-01' EXEC (@SQL)SELECT NAME FROM SYS.TABLES WHERE NAME ='A_0229' --A_0229
就用日期循环就可以了,主要用到语句
executesql('create table '+@变量表名+'')
这就是建表的原因
SET @YEAR=2010
SELECT @SQL=ISNULL(@SQL,'')+'CREATE TABLE A_'+RIGHT(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01'),112),4)+'(COL1 INT);'
FROM MASTER..SPT_VALUES WHERE TYPE='P'
AND DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01') < LTRIM(@YEAR+1)+'-01-01'
EXEC (@SQL)
SET @YEAR=2010
SET @TB_FIRST_NAME='A_'
SET @COLS='COL1 INT
,COL2 VARCHAR(50)'
SELECT @SQL=ISNULL(@SQL,'')
+'CREATE TABLE '
+@TB_FIRST_NAME
+RIGHT(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01'),112),4)
+'('+@COLS+');'
FROM MASTER..SPT_VALUES WHERE TYPE='P'
AND DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01') < LTRIM(@YEAR+1)+'-01-01'
EXEC (@SQL)SELECT NAME FROM SYS.TABLES WHERE NAME LIKE 'A_%'
--365行
select distinct convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))
from master..spt_values a,
master..spt_values b
where a.number between 1 and 12 and a.type='p'
and b.number between 1 and 31 and a.type='p'
and isdate(ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))=1
order by convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))
declare @name varchar(max),@year intselect @year=2010select distinct convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number)) as name
into rqb ---放到日期表
from master..spt_values a,
master..spt_values b
where a.number between 1 and 2 and a.type='p' --月份以 between 1 and 2 为例,你改成1到12
and b.number between 1 and 3 and a.type='p' --日期以 between 1 and 3 为例,你改成1到31
and isdate(ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))=1
order by convert(datetime,ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))select @name=isnull(@name,' create table tbl_smresult_')+ltrim(month(name))+ltrim(day(name))+'(id int); create table tbl_smresult_'
from rqb set @name=left(@name,len(@name)-26)exec(@name)
drop table rqb
判断了。isdate(ltrim(@year)+'-'+ltrim(a.number) +'-'+ltrim(b.number))=1你可以试下执行,2010 生成是365行记录
DECLARE @YEAR INT,@SQL VARCHAR(MAX),@TB_FIRST_NAME VARCHAR(100),@COLS VARCHAR(8000)
SET @YEAR=2008
SET @TB_FIRST_NAME='A_'
SET @COLS='COL1 INT
,COL2 VARCHAR(50)'
SELECT @SQL=ISNULL(@SQL,'')
+'CREATE TABLE '
+@TB_FIRST_NAME
+RIGHT(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01'),112),4)
+'('+@COLS+');'
FROM MASTER..SPT_VALUES WHERE TYPE='P'
AND DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-01-01') < LTRIM(@YEAR+1)+'-01-01'
EXEC (@SQL)SELECT NAME FROM SYS.TABLES WHERE NAME ='A_0229'
--A_0229