Sql Server插入隨機數

標籤：

--處理性別隨機
select (case when round(rand()*10,0)>5 then ‘男‘ else ‘女‘ end),
--處理時間段範圍內隨機
select dateadd(dd,round(datediff(dd,‘1992-01-01‘,‘1995-01-01‘)*rand(),0),‘1992-01-01‘)

--添加外鍵
ALTER TABLE [dbo].[StudnetInfo] WITH CHECK ADD CONSTRAINT [FK_StudnetInfo_StudentClass] FOREIGN KEY([classId])
REFERENCES [dbo].[StudentClass] ([ClassID])
--刪除外鍵
alter table StudnetInfo drop constraint [FK_StudnetInfo_StudentClass]
truncate table StudnetInfo
--查詢所有關聯約束
exec sp_helpconstraint StudnetInf

--遊標處理根據其他表限制的複雜插入
declare @i int, @j int [email protected]是 @classsum 遍曆的記錄數，控制while迴圈;@j記錄全部迴圈的序號
set @j=0
declare cur_temp cursor for select ClassID,ClassSum from StudentClass
open cur_temp
declare @classid varchar(5)
declare @classsum int
fetch next from cur_temp into @classid,@classsum
WHILE @@FETCH_STATUS =0
BEGIN
--print @classid --班級+年份+3位的排序號 0012015001 9位
set @i=0
print @classsum
while(@i<@classsum)
begin
insert into StudnetInfo (StuId,StuName,classId,sex,rx_time,bron)
--處理3位的排序號
select @classid+‘2015‘+right(‘00‘+convert(varchar(3),@j),3) as 學號,
‘鄒敏‘+convert(varchar(5),@j) as 姓名,
@classid as 班級編號,
(case when round(rand()*10,0)>5 then ‘男‘ else ‘女‘ end) as 性別,
convert(datetime,left(year(GETDATE()),4)+‘-09-01‘) as 入學時間,
dateadd(dd,round(datediff(dd,‘1992-01-01‘,‘1995-01-01‘)*rand(),0),‘1992-01-01‘) as 出生時間
--from StudentClass
set @[email protected]+1
set @[email protected]+1
end
fetch next from cur_temp into @classid,@classsum
end
close cur_temp
deallocate cur_temp
print @i
print @j

--left(rand())處理取整

--round(rand(),0)四捨五入取值

Sql Server插入隨機數