Sqlserver新建随机测试数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205

USE Test --使用数据库Test（如果没有则需要新建一个）   ----1.新建一个users表 create table users( uId int primary key identity(1,1), uName varchar(50) null, uSex bit null default(1), uPwd varchar(50) null, uEmail varchar(100) null, uPwdfindone nvarchar(50) null, uPwdfindtwo nvarchar(50) null, uPwdfindthree nvarchar(50) null, uPwdfindanswer1 nvarchar(50) null, uPwdfindanswer2 nvarchar(50) null, uPwdfindanswer3 nvarchar(50) null, uCity varchar(50) null, uBirthyear int null, uBirthmonth int null, uBirthday int null, uScore int null, uRegistertime datetime null default(getdate()), uRegisterIP varchar(50) null ) GO   ----2.循环向users表插入随机数据 declare @s int --循环起点 declare @n int --年 declare @y int --月 declare @r int --日 declare @h int --时 declare @f int --分 declare @m int --秒 declare @mm int --毫秒 declare @t varchar(30) --注册时间 declare @address varchar(10)--地址 declare @email varchar(20) --邮箱 declare @xb int --性别 declare @xm varchar(20) --姓名 declare @ma varchar(20) --密码 declare @mq1 varchar(20) --密码问题1 declare @mq2 varchar(20) --密码问题2 declare @mq3 varchar(20) --密码问题3 declare @mqa1 varchar(20) --密码答案1 declare @mqa2 varchar(20) --密码答案2 declare @mqa3 varchar(20) --密码答案3 declare @bn int --出生年份 declare @by int --出生月 declare @br int --出生日 declare @fs int --分数 declare @ip1 int --ip地址第1段 declare @ip2 int --ip地址第2段 declare @ip3 int --ip地址第3段 declare @ip4 int --ip地址第4段 declare @ip varchar(50) --ip地址 set @s=1 --设置循环起点 while(@s<1000000) --设置循环终点 begin declare @id varchar(10) set @id=CAST(@s as varchar(10)) begin --注册时间 set @n=1990+abs(checksum(newid()))%(2014-1990+1) set @y=1+abs(checksum(newid()))%(12-1+1) set @r=1+abs(checksum(newid()))%(31-1+1) if(@y=2)--当前月份是2月 begin if((@n%4=0 and @n%100 <>0) or @n%400=0)--闰年判断 set @r=1+abs(checksum(newid()))%(29-1+1) else set @r=1+abs(checksum(newid()))%(28-1+1) end else begin if(@y%4=0 or @y%6=0 or @y%9=0 or @y%11=0)--4、6、9、11月最多30天 set @r=1+abs(checksum(newid()))%(30-1+1) end declare @yy varchar(5) set @yy=CAST(@y as varchar(5)) if(@y<10) set @yy='0'+CAST(@y as varchar(5)) declare @rr varchar(5) set @rr=CAST(@r as varchar(5)) if(@r<10) set @rr='0'+CAST(@r as varchar(5)) set @h=1+abs(checksum(newid()))%(23-1+1) set @f=1+abs(checksum(newid()))%(59-1+1) declare @ff varchar(5)--分，用于格式化分钟 set @ff=CAST(@f as varchar(5)) if(@f<10) set @ff='0'+CAST(@f as varchar(5)) set @m=1+abs(checksum(newid()))%(59-1+1) declare @miao varchar(5)--秒，用于格式化秒钟 set @miao=CAST(@m as varchar(5)) if(@m<10) set @miao='0'+CAST(@m as varchar(5)) set @mm=1+abs(checksum(newid()))%(999-1+1) set @t=CAST(@n as varchar(5))+'-'+@yy+'-'+@rr+' '+CAST(@h as varchar(5))+':'+@ff+':'+@miao+':'+CAST(@mm as varchar(5)) declare @time datetime set @time=convert(datetime,@t) end begin --城市及邮箱 if(@s%2=0) begin set @xb=0 set @email=@id+'@qq.com' set @address='Guangzhou' end else begin   set @xb=1   set @email=@id+'@msn.com' set @address='Berlin' end if(@s%11=0) begin set @email=@id+'@nas.com' set @address='Strasbourg' end if(@s%22=0) begin set @email=@id+'@skyweb.com' set @address='Campinas' end if(@s%33=0) begin set @email=@id+'@gly.com' set @address='Xiamen' end if(@s%14=0) begin set @email=@id+'@bl.com' set @address='Dalian' end if(@s%15=0) begin set @email=@id+'@hpjx.com' set @address='Hangzhou' end if(@s%16=0) begin set @email=@id+'@rgm.com' set @address='Wuhan' end if(@s%27=0) begin set @email=@id+'@tf.com' set @address='Chengdu' end if(@s%58=0) begin set @email=@id+'@fj.com' set @address='Lijiang' end if(@s%39=0) begin set @email=@id+'@px.com' set @address='Lyon' end end begin --姓名、性别、密码问题、密码答案等 set @xm='uname'+@id set @ma='upwd'+@id set @mq1='upwdfindone'+@id set @mq2='upwdfindtwo'+@id set @mq3='upwdfindthree'+@id set @mqa1='upwd1answer'+@id set @mqa2='upwd2answer'+@id set @mqa3='upwd3answer'+@id set @bn=1980+abs(checksum(newid()))%(1995-1980+1) set @by=1+abs(checksum(newid()))%(12-1+1) set @br=1+abs(checksum(newid()))%(31-1+1) if(@by=2)--当前月份是2月 begin if((@bn%4=0 and @bn%100 <>0) or @bn%400=0)--闰年判断 set @br=1+abs(checksum(newid()))%(29-1+1) else set @br=1+abs(checksum(newid()))%(28-1+1) end else begin if(@by%4=0 or @by%6=0 or @by%9=0 or @by%11=0)--4、6、9、11月最多30天 set @br=1+abs(checksum(newid()))%(30-1+1) end set @fs=11+abs(checksum(newid()))%(150-11+1) end begin --ip地址 set @ip1=1+abs(checksum(newid()))%(254-1+1) set @ip2=1+abs(checksum(newid()))%(254-1+1) set @ip3=1+abs(checksum(newid()))%(254-1+1) set @ip4=1+abs(checksum(newid()))%(254-1+1) set @ip=CAST(@ip1 as varchar(5))+'.'+CAST(@ip2 as varchar(5))+'.'+CAST(@ip3 as varchar(5))+'.'+CAST(@ip4 as varchar(5)) end begin --执行sql语句 insert into users(uName,uSex,uPwd,uEmail,uPwdfindone,uPwdfindtwo,uPwdfindthree,uPwdfindanswer1,uPwdfindanswer2, uPwdfindanswer3,uCity,uBirthyear,uBirthmonth,uBirthday,uScore,uRegistertime,uRegisterIP) values( @xm,@xb,@ma,@email,@mq1,@mq2,@mq3,@mqa1,@mqa2,@mqa3,@address,@bn,@by,@br,@fs,@t,@ip ) end set @s+=1   end     ----3.检查数据新增是否执行成功 --select * from users