alter procedure doTable as DECLARE @A1 VARCHAR(200),@A2 VARCHAR(200),@A3 varchar(1000),@A4 varchar(200) declare @i int declare @j int declare @length int declare @questionId varchar(50) declare @areaid varchar(50) declare @creatuser varchar(50) declare @content varchar(100) declare @replyAccount varchar(50) declare @replyMembeId varchar(50) declare @postTime datetime DECLARE test CURSOR FOR SELECT 标题,id,内容,分类 FROM view1 OPEN test fetch next from test into @a1,@a2,@a3 ,@a4
while @@fetch_status<>-1 begin ---设定第几条数据 set @j=0
--------去除左右空格 set @a3=rtrim(ltrim(@a3)) set @i=charindex('|||',@a3) while @i>=1 begin
---第一条数据的话 if @j=0 begin set @questionId=newid() -------添加到问题列表中 set @content=left(@a3,@i-1)
set @j=@j+1
---获得任意一个用户 select top 1 @creatuser=account from member order by newid() ---获得任意一个地区 select top 1 @areaid=id from memberarea order by newid()
insert into question (ID,F_CATALOGID,F_AREAID,TITLE,CONTENT,POSTUSER,createuser) values (@questionId,@a2,@areaid,@a1,@content,@creatuser,@creatuser)
end ---如果不是第一条记录，那就是问题的回复 else begin
-------添加回复问题的表中 ---获得任意一个用户 select top 1 @replyAccount= account,@replyMembeId=id from member order by newid() --一年内的随机时间 set @postTime=dateadd(day, -cast(ceiling(rand() *365) as int),convert(char(50),getdate(),20))
insert into QUESTIONREPLY (F_QUESTIONID,TITLE,CONTENT,REPLYUSER,CREATEDATE,replydate,createuser,posta,postb) values (@questionId,@a1,left(@a3,@i-1),@replyAccount,@postTime,@postTime,@replyMembeId,cast(ceiling(rand() *2) as int)-1,cast(ceiling(rand() *2) as int)-1) end
set @a3=substring(@a3,@i+3,len(@a3)-@i) set @i=charindex('|||',@a3)
end
if @a3<>'\' begin if @j=0 begin -------只有一条，添加到问题列表中 ---获得任意一个用户 select top 1 @creatuser=account from member order by newid() ---获得任意一个地区 select top 1 @areaid=id from memberarea order by newid()
set @questionId=newid() insert into question (id,F_CATALOGID,F_AREAID,TITLE,CONTENT,POSTUSER,createuser) values (@questionId,@a2,@areaid,@a1,@a3,@creatuser,@creatuser) end else begin
-------添加回复问题的表中 ---获得任意一个用户 select top 1 @replyAccount= account,@replyMembeId=id from member order by newid() --一年内的随机时间 set @postTime=dateadd(day, -cast(ceiling(rand() *365) as int),convert(char(50),getdate(),20))
insert into QUESTIONREPLY (F_QUESTIONID,TITLE,CONTENT,REPLYUSER,CREATEDATE,replydate,createuser,posta,postb) values (@questionId,@a1,@a3,@replyAccount,@postTime,@postTime,@replyMembeId,cast(ceiling(rand() *2) as int)-1,cast(ceiling(rand() *2) as int)-1) end end
fetch next from test into @a1,@a2,@a3 ,@a4 end close test deallocate test
execute doTable
select 内容 from view1
select count(*) from question
select count(*) from questionreply
delete from question
delete from questionreply
转载于:https://www.cnblogs.com/n666/archive/2009/10/23/2191059.html
