CREATE PROCEDURE addss @bytes int, @num int AS declare @errorcode int , @i int, @j int, @s varchar(20), @temp int, @num1 varchar(4), @num2 varchar(4), @num3 varchar(4), @num4 varchar(4), @num5 varchar(4), @num6 varchar(4), @num7 varchar(4), @num8 varchar(4), @num9 varchar(4)set @i=1 set @j=1 while @i<=@num begin set @s=''
while @j<=@bytes begin set @num1=convert(varchar(4),rand(checksum(newid()))*10) set @num2=convert(varchar(4),rand(checksum(newid()))*10) set @num3=convert(varchar(4),rand(checksum(newid()))*10) set @num4=convert(varchar(4),rand(checksum(newid()))*10) set @num5=convert(varchar(4),rand(checksum(newid()))*10) set @num6=convert(varchar(4),rand(checksum(newid()))*10) set @num7=convert(varchar(4),rand(checksum(newid()))*10) set @num8=convert(varchar(4),rand(checksum(newid()))*10) set @num9=convert(varchar(4),rand(checksum(newid()))*10)
set @temp= case rand(checksum(newid()))*10 when 1 then @num1 when 2 then @num2 when 3 then @num3 when 4 then @num4 when 5 then @num5 when 6 then @num6 when 7 then @num7 when 8 then @num8 when 9 then @num9 end set @s=@s+@temp set @j=@j+1 end begin tran exec('insert into dc([code_b]) values ('+cast(@s as varchar)+')') if @@error<>0 begin rollback tran continue end set @i=@i+1 commit tran --end return 1 GO
create proc addss @bytes int, @num int as declare @t table(a int) declare @tem varchar(100) while @num>0 begin set @tem=right(rand(checksum(newid())),@bytes) while @tem in (select a from @t) set @tem=right(rand(checksum(newid())),@bytes) insert @t values(@tem) set @num=@num-1 end insert dc select a from @t go
你的位数是代表什么意思?@num应该是要插入多少个吧?
或:create proc addss @bytes int, @num int as declare @tem varchar(100) while @num>0 begin set @tem=right(rand(checksum(newid())),@bytes) while exists (select 1 from @t where a=@tem) set @tem=right(rand(checksum(newid())),@bytes) insert dc values(@tem) set @num=@num-1 end go
zjcxc(邹建) 位数就是一个数的位数啊,比如存储过程输入两个参数,8,10000,意思是说生成10000个8位数的数啊表很简单,表名是DC id 种子数 code_b 字符型,设了主键,不能重复 stat SQL自动初始化为1,这是一个生成数的存储过程,如果要提取,提取后的数就设为0了
--创建插入随机数的存储过程 create proc adds @bytes int, @num int as declare @tem varchar(20) while @num>0 begin set @tem=right(cast(rand(checksum(newid()))*power(10,@bytes) as decimal(20)),@bytes) exec('insert into tb(id) values('+@tem+')') if @@error=0 set @num=@num-1 end go
--下面是数据测试create proc adds @bytes int, @num int as declare @tem varchar(20) while @num>0 begin set @tem=right(cast(rand(checksum(newid()))*power(10,@bytes) as decimal(20)),@bytes) exec('insert into tb(id) values('+@tem+')') if @@error=0 set @num=@num-1 end go--创建测试表 create table tb(id varchar(20) primary key)--调用存储过程测试 exec adds 8,20--显示结果 select * from tb go --删除测试环境 drop table tb drop proc adds
CREATE PROCEDURE addss @bytes int, @num int AS begin declare @a varchar(100),@b varchar(100) declare @sql varchar(8000) select @a='1'+replicate('0',@bytes-1),@b='9'+replicate('0',@bytes-1)while @num>0 begin set @sql='insert dc(code_b) select * from (select distinct * from ( select top '+rtrim(@num)+' '+@a+'+cast(rand(checksum(newid()))*'+@b+' as int) as code_b from sysobjects A,sysobjects B,sysobjects C) A where code_b not in (select code_b from dc) ) B order by newid()' --print @sql exec(@sql)set @num=@num-@@rowcount --判断是否有足够的记录,不够再来。endend
下面是测试结果: create table dc(id int identity,code_b varchar(10) primary key) go exec addss 8,1000000 go (所影响的行数为 994453 行) (所影响的行数为 5490 行) (所影响的行数为 56 行) (所影响的行数为 1 行) --从上面可以看到四次才完成一百万条记录,花时1分40秒select top 10 * from dc go id code_b ----------- ---------- 568837 10000040 189675 10000366 738114 10000479 305645 10000521 138923 10000580 813520 10000650 111316 10000714 964334 10000818 220821 10000881 609947 10000998 select top 10 * from dc order by id go id code_b ----------- ---------- 1 28140679 2 73964209 3 45831253 4 48785348 5 17076774 6 38143620 7 22609802 8 50737073 9 50985091 10 15166383 drop table dc
先 create unique index removedups on dc(code_b) with IGNORE_DUP_KEY再往里面插数据 插完判断条数是否够了,不够再查,一直到够了为止
也可以这样,还是上面的方法,只是没插入一条就判断@@rowcount,为0就再生成一次随机数,插入
要改一下,上面的过程可以会受数据类型限制,这样改就不会了:CREATE PROCEDURE addss @bytes int, @num int AS begindeclare @a varchar(8000),@i int,@sql varchar(8000) select @a='rtrim(cast(1+rand(checksum(newid()))*9 as int))' set @i=1while @i<@bytes select @a=@a+'+rtrim(cast(rand(checksum(newid()))*10 as int))',@i=@i+1while @num>0 begin set @sql='insert dc(code_b) select * from (select distinct * from ( select top '+rtrim(@num)+' '+@a+' as code_b from sysobjects A,sysobjects B,sysobjects C) A where code_b not in (select code_b from dc) ) B order by newid()' --print @sql exec(@sql)set @num=@num-@@rowcount --判断是否有足够的记录,不够再来。endend
如果位数超过18位用上面的,位数不超过18用这个要快得多。CREATE PROCEDURE addss @bytes int, @num int AS begin declare @a varchar(100),@b varchar(100) declare @sql varchar(8000) select @a='1'+replicate('0',@bytes-1),@b='9'+replicate('0',@bytes-1)while @num>0 begin set @sql='insert dc(code_b) select * from (select distinct * from ( select top '+rtrim(@num)+' '+@a+'+cast(rand(checksum(newid()))*'+@b+' as bigint) as code_b from sysobjects A,sysobjects B,sysobjects C) A where code_b not in (select code_b from dc) ) B order by newid()' --print @sql exec(@sql)set @num=@num-@@rowcount --判断是否有足够的记录,不够再来。endend
AS
declare @errorcode int ,
@i int,
@j int,
@s varchar(20),
@temp int,
@num1 varchar(4),
@num2 varchar(4),
@num3 varchar(4),
@num4 varchar(4),
@num5 varchar(4),
@num6 varchar(4),
@num7 varchar(4),
@num8 varchar(4),
@num9 varchar(4)set @i=1
set @j=1 while @i<=@num
begin
set @s=''
while @j<=@bytes
begin
set @num1=convert(varchar(4),rand(checksum(newid()))*10)
set @num2=convert(varchar(4),rand(checksum(newid()))*10)
set @num3=convert(varchar(4),rand(checksum(newid()))*10)
set @num4=convert(varchar(4),rand(checksum(newid()))*10)
set @num5=convert(varchar(4),rand(checksum(newid()))*10)
set @num6=convert(varchar(4),rand(checksum(newid()))*10)
set @num7=convert(varchar(4),rand(checksum(newid()))*10)
set @num8=convert(varchar(4),rand(checksum(newid()))*10)
set @num9=convert(varchar(4),rand(checksum(newid()))*10)
set @temp=
case rand(checksum(newid()))*10
when 1 then @num1
when 2 then @num2
when 3 then @num3
when 4 then @num4
when 5 then @num5
when 6 then @num6
when 7 then @num7
when 8 then @num8
when 9 then @num9
end
set @s=@s+@temp
set @j=@j+1
end
begin tran
exec('insert into dc([code_b]) values ('+cast(@s as varchar)+')')
if @@error<>0
begin
rollback tran
continue
end
set @i=@i+1
commit tran
--end
return 1
GO
您的那行代码我试过了,不行啊,还是死机的,并没有加到SQL中啊:(
ADOStoredProc3.Close ;
ADOStoredProc3.Parameters[1].Value :=strtoint(edit1.Text );
ADOStoredProc3.Parameters[2].Value :=strtoint(edit2.Text );
ADOStoredProc3.Prepared ;
ADOStoredProc3.ExecProc ;
@bytes int,
@num int
as
declare @t table(a int)
declare @tem varchar(100)
while @num>0
begin
set @tem=right(rand(checksum(newid())),@bytes)
while @tem in (select a from @t)
set @tem=right(rand(checksum(newid())),@bytes)
insert @t values(@tem)
set @num=@num-1
end
insert dc select a from @t
go
@bytes int,
@num int
as
declare @tem varchar(100)
while @num>0
begin
set @tem=right(rand(checksum(newid())),@bytes)
while exists (select 1 from @t where a=@tem)
set @tem=right(rand(checksum(newid())),@bytes)
insert dc values(@tem)
set @num=@num-1
end
go
位数就是一个数的位数啊,比如存储过程输入两个参数,8,10000,意思是说生成10000个8位数的数啊表很简单,表名是DC
id 种子数
code_b 字符型,设了主键,不能重复
stat SQL自动初始化为1,这是一个生成数的存储过程,如果要提取,提取后的数就设为0了
您好,如果我没理解错的话,您的大意是:生成一个随机数,位数不确定,为了防止重复,您在表中搜索了?我是想输入两个数,一个是位数,一个是个数,如8,100000,表示生成长100000个8位数的数,一定要是8位的,还有,这些数可以有几十万或上百万,如果每加一个数就搜一遍表,可能不太好吧,效率我想不会很高的,是吧
create proc adds
@bytes int,
@num int
as
declare @tem varchar(20)
while @num>0
begin
set @tem=right(cast(rand(checksum(newid()))*power(10,@bytes) as decimal(20)),@bytes)
exec('insert into tb(id) values('+@tem+')')
if @@error=0
set @num=@num-1
end
go
@bytes int,
@num int
as
declare @tem varchar(20)
while @num>0
begin
set @tem=right(cast(rand(checksum(newid()))*power(10,@bytes) as decimal(20)),@bytes)
exec('insert into tb(id) values('+@tem+')')
if @@error=0
set @num=@num-1
end
go--创建测试表
create table tb(id varchar(20) primary key)--调用存储过程测试
exec adds 8,20--显示结果
select * from tb
go
--删除测试环境
drop table tb
drop proc adds
所以应该这样生成数字:10,000,000+rand()*90,000,000
且在插入数字时应该先判断那些记录重复了,还有那些记录表里已存在。
AS
begin
declare @a varchar(100),@b varchar(100)
declare @sql varchar(8000)
select @a='1'+replicate('0',@bytes-1),@b='9'+replicate('0',@bytes-1)while @num>0
begin
set @sql='insert dc(code_b) select * from (select distinct * from (
select top '+rtrim(@num)+' '+@a+'+cast(rand(checksum(newid()))*'+@b+' as int) as code_b from
sysobjects A,sysobjects B,sysobjects C) A where code_b not in (select code_b from
dc) ) B order by newid()'
--print @sql
exec(@sql)set @num=@num-@@rowcount --判断是否有足够的记录,不够再来。endend
create table dc(id int identity,code_b varchar(10) primary key)
go
exec addss 8,1000000
go
(所影响的行数为 994453 行)
(所影响的行数为 5490 行)
(所影响的行数为 56 行)
(所影响的行数为 1 行)
--从上面可以看到四次才完成一百万条记录,花时1分40秒select top 10 * from dc
go
id code_b
----------- ----------
568837 10000040
189675 10000366
738114 10000479
305645 10000521
138923 10000580
813520 10000650
111316 10000714
964334 10000818
220821 10000881
609947 10000998
select top 10 * from dc order by id
go
id code_b
----------- ----------
1 28140679
2 73964209
3 45831253
4 48785348
5 17076774
6 38143620
7 22609802
8 50737073
9 50985091
10 15166383
drop table dc
create unique index removedups on dc(code_b) with IGNORE_DUP_KEY再往里面插数据
插完判断条数是否够了,不够再查,一直到够了为止
AS
begindeclare @a varchar(8000),@i int,@sql varchar(8000)
select @a='rtrim(cast(1+rand(checksum(newid()))*9 as int))'
set @i=1while @i<@bytes
select @a=@a+'+rtrim(cast(rand(checksum(newid()))*10 as int))',@i=@i+1while @num>0
begin
set @sql='insert dc(code_b) select * from (select distinct * from (
select top '+rtrim(@num)+' '+@a+' as code_b from
sysobjects A,sysobjects B,sysobjects C) A where code_b not in (select code_b from
dc) ) B order by newid()'
--print @sql
exec(@sql)set @num=@num-@@rowcount --判断是否有足够的记录,不够再来。endend
AS
begin
declare @a varchar(100),@b varchar(100)
declare @sql varchar(8000)
select @a='1'+replicate('0',@bytes-1),@b='9'+replicate('0',@bytes-1)while @num>0
begin
set @sql='insert dc(code_b) select * from (select distinct * from (
select top '+rtrim(@num)+' '+@a+'+cast(rand(checksum(newid()))*'+@b+' as bigint) as code_b from
sysobjects A,sysobjects B,sysobjects C) A where code_b not in (select code_b from
dc) ) B order by newid()'
--print @sql
exec(@sql)set @num=@num-@@rowcount --判断是否有足够的记录,不够再来。endend