例如,我有一个话单表 tableA表 因为话务单太多,想做成按月生成 tableA201101,tableA201102,tableA201103 ... 数据都是从 tableA表中截取 tableA表中的记录如下小ID, NUM, FCDATE
1 4008001 2011-01-12
2 4008002 2011-01-13
3 4008003 2011-02-12
4 4008004 2011-02-14
5 4008005 2011-03-15这样想动态生成三张表 tableA201101,tableA201102,tableA201103 ...
tableA201101
ID, NUM, FCDATE
1 4008001 2011-01-12
2 4008002 2011-01-13tableA201102
ID, NUM, FCDATE
3 4008003 2011-02-12
4 4008004 2011-02-14...........在线请教高手
1 4008001 2011-01-12
2 4008002 2011-01-13
3 4008003 2011-02-12
4 4008004 2011-02-14
5 4008005 2011-03-15这样想动态生成三张表 tableA201101,tableA201102,tableA201103 ...
tableA201101
ID, NUM, FCDATE
1 4008001 2011-01-12
2 4008002 2011-01-13tableA201102
ID, NUM, FCDATE
3 4008003 2011-02-12
4 4008004 2011-02-14...........在线请教高手
AS
BEGIN
declare @i varchar(10)
declare @sql varchar(8000)
set @i='201101'
while exists(select 1 from tableA where convert(varchar(6),FCDATE,112)=@i)
begin
set @tb='tableA'+@i
set @sql='select * into '+@tb+ ' from tableA'
exec (@sql)
set @i=convert(varchar(6),dateadd(month,1,convert(datetime,(@i+'01'))),112)
end
END
先要谢谢老兄,,,,,,,,,不过,不是我想要的..你是月份都是递增,并且从tableA表中取数据都没有给出思路...
你要求是什么?
--按你的要求不是更简单?!
CREATE PROCEDURE proc_month(@i varchar(10))
AS
BEGIN
declare @tb varchar(20)
declare @sql varchar(8000)
if exists(select 1 from tableA where convert(varchar(6),FCDATE,112)=@i)
begin
set @tb='tableA'+@i
set @sql='select * into '+@tb+ ' from tableA'
exec (@sql)
end
END
的方案,可以解决啊
gocreate table tb(ID int,NUM int,FCDATE datetime)
insert into tb values(1,4008001,'2011-01-12')
,(2,4008002,'2011-01-13')
,(3,4008003,'2011-02-12')
,(4,4008004,'2011-02-14')
,(5,4008005,'2011-03-15');
select distinct convert(varchar(6),FCDATE,112) as FCDATE into #DF
from tbwhile 1=1
begin
declare @STR varchar(5000);
declare @FCDATE varchar(6);
select @FCDATE=FCDATE from #DF;
if @@ROWCOUNT=0
break;
exec('select * into test.dbo.table'+@FCDATE+' from tb where convert(varchar(6),FCDATE,112)='+@FCDATE)
delete from #DF where FCDATE=@FCDATE
enddrop table #DF
难过..大家想得都太简单了....难道大家就没有碰到这样的项目吗...一个项目初始是是一张记录所有的记录..突然一天做查询报表时速度太慢了..公司考虑按月生成表存储数据...就是用作业调用定时每天晚上1:00执行,如当前时间不是本月的最后一天,则不生成新表,,数据还是原表存储 tableA 存当前月数据, tableA201101.....这些带数字的存历史数据,按月截取入相应的表.........
declare @STR varchar(5000); 可以不要 做测试时写的 ~
还TM每天晚上1:00执行判断下是不是本月最后一天!!!!!
原表的数据转到历史数据表后,你不会给的样本存储过程里加个数据从原表delete?每个语句都要别人给你写?
提供思路都这地步了你自己还弄不出来还难过,你难过个P难过抱歉,失态了
一点脑子都不动就在这否定别人的答案,态度还那么...
唉,顺便说一句,你这个作法其实本身就是不妥的,每月建张表完全不靠谱
加索引和优化sql才是王道
create table tableA(ID int identity(1,1), NUM varchar(10), FCDATE varchar(10))
goCREATE trigger tableA_i on tableA after insert
as
declare @sql varchar(8000),@tablename varchar(20),@sql_i varchar(8000),@sql_i1 varchar(8000)
SET @SQL=''
select @sql=@SQL+'create table '+a.tablename+'(ID int identity(1,1), NUM varchar(10), FCDATE varchar(10))' from (
select distinct 'tableA'+convert(varchar(6),convert(datetime,FCDATE),112) as tablename
from INSERTED
where 'tableA'+convert(varchar(6),convert(datetime,FCDATE),112) not in (select name from sys.tables where name like 'tableA%')) as a
exec(@sql)
SET @SQL_I=''
select @sql_i=@SQL_I+'insert into tableA'+convert(varchar(6),convert(datetime,FCDATE),112)+'(NUM,FCDATE)'+' SELECT '''+NUM+''','''+FCDATE+'''' from inserted
EXEC(@SQL_I)---测试数据
INSERT INTO TABLEA(NUM,FCDATE)
SELECT '4008001', '2011-01-14' UNION ALL
SELECT '4008002', '2011-02-13' UNION ALL
SELECT '4008001', '2011-03-14' UNION ALL
SELECT '4008002', '2011-04-13'SELECT * FROM tableA201101
SELECT * FROM tableA201102
SELECT * FROM tableA201103
SELECT * FROM tableA201104
-----------------------------------------------------
ID NUM FCDATE
----------- ---------- ----------
1 4008001 2011-01-14(1 行受影响)ID NUM FCDATE
----------- ---------- ----------
1 4008002 2011-02-13(1 行受影响)ID NUM FCDATE
----------- ---------- ----------
1 4008001 2011-03-14(1 行受影响)ID NUM FCDATE
----------- ---------- ----------
1 4008002 2011-04-13(1 行受影响)
用触发器做,我在SQL2008已经测试通了
#3楼 得分:0回复于:2011-08-10 16:31:37SQL codeCREATE PROCEDURE proc_month
AS
BEGIN
declare @i varchar(10)
declare @sql varchar(8000)
set @i='201101'
while exists(select 1 from tableA where convert(varchar(6),FCDATE,112)=@i)
begin
set @tb='tableA'+@i
set @sql='select * into '+@tb+ ' from tableA'
exec (@sql)
set @i=convert(varchar(6),dateadd(month,1,convert(datetime,(@i+'01'))),112)
end
END