create table A(dtime varchar(60))
insert into A select '2006-6-29 15:13:36'
select dtime from A -测试数据
declare @tbname varchar(60)
set @tbname=(select dtime from A)declare @temp varchar(8000)
set @temp='if not exists (select * from dbo.sysobjects where id =object_id(N''[dbo]."'+@tbname+'"'') and objectproperty(id, N''IsUserTable'') = 1) begin create table "'+@tbname+'" (dtime varchar(60)) insert into "'+@tbname+'" select '''+@tbname+''' end'
exec(@temp)/*结果生成一个"2006-6-29 15:13:36"的用户表,以下是数据(数据自己任加)
2006-6-29 15:13:36
*/
insert into A select '2006-6-29 15:13:36'
select dtime from A -测试数据
declare @tbname varchar(60)
set @tbname=(select dtime from A)declare @temp varchar(8000)
set @temp='if not exists (select * from dbo.sysobjects where id =object_id(N''[dbo]."'+@tbname+'"'') and objectproperty(id, N''IsUserTable'') = 1) begin create table "'+@tbname+'" (dtime varchar(60)) insert into "'+@tbname+'" select '''+@tbname+''' end'
exec(@temp)/*结果生成一个"2006-6-29 15:13:36"的用户表,以下是数据(数据自己任加)
2006-6-29 15:13:36
*/
declare @count int
select @count=count(*) from 表A
declare aaa cursor scroll
for
select 时间 from 表A
open aaa
declare @i int,@j nvarchar(30)
set @i=1
while @i<=@count
begin
fetch absolute @i from aaa into @j
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@j) AND type in (N'U'))
insert into 新表 select * from 表A where 时间=@j
set @i=@i+1
end
close aaa
deallocate aaa
insert A select '2006-6-29 13:13:36'
union all select '2006-6-29 13:14:36'
union all select '2006-6-29 13:15:36'
union all select '2006-6-29 14:13:36'
union all select '2006-6-29 15:13:36'--1.建立所需要的表
declare @s1 varchar(8000)
select @s1=' '
select @s1=@s1
+'
IF not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].['+Mydate+']'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
CREATE TABLE [dbo].['+Mydate+'](
[Mydate] [datetime] NOT NULL
)
'
from
(select distinct convert(varchar(10),Mydate,120)+' '+convert(varchar, datename(hh,Mydate)) [Mydate] from A) b
exec (@s1)
--2.插入相应的数据
declare @s2 varchar(8000)
select @s2=''
select @s2=@s2+' insert into ['+Mydate+'] '
+'select * from A
where convert(varchar(10),Mydate,120)+'' ''+convert(varchar, datename(hh,Mydate))='+quotename(Mydate,'''')
from
(select distinct convert(varchar(10),Mydate,120)+' '+convert(varchar, datename(hh,Mydate)) [Mydate] from A) b
exec(@s2)drop table A
go
declare @count int
select @count=count(*) from "111"
declare aaa cursor scroll
for
select datename(year,shijie) + datename(month,shijie) + datename(day,shijie) + datename(hour,shijie) from "111"
open aaa
declare @i int,@a nvarchar(30)
set @i=1
while @i<=@count
begin
fetch absolute @i from aaa into @a
if exists (select * from dbo.sysobjects where id = object_id(@a) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
insert into (@a) select * from "111" where (这里的条件是什么啊?比如说只要是表名里小时为13点的 所有13点到14点之间的数据全部插入13点的那张表中)
else
create table [@a] ([shijie] [datetime] NOT NULL)ON [PRIMARY] insert into [@a] select * from "111" where(这里的条件是什么啊?比如说只要是表名里小时为13点的 所有13点到14点之间的数据全部插入13点的那张表中)
set @i=@i+1
end
close aaa
deallocate aaa