sqlserver利用存储过程造随机数据

–dxp_gp_1w造数据存储过程
DROP PROCEDURE add_list;
CREATE PROCEDURE add_list(@num int)
AS
DECLARE @i INT
DECLARE @RandomStr CHAR(1)
DECLARE @RandomStr2 CHAR(1)
DECLARE @RandomStr3 CHAR(5)
DECLARE @Counter INT
DECLARE @Counter2 INT
DECLARE @Counter3 INT
DECLARE @CharPool CHAR(62)
declare @Date_start datetime
declare @Date_end datetime
declare @test_date datetime
declare @test_decimal decimal(18)
declare @test_int INT
declare @test_float float(53)
DECLARE @NumBegin Int=60
–随机数的最小值
DECLARE @NumEnd Int=100
–随机数的最大值
DECLARE @Decimal Int=2
–保留小数点几位
set @Date_start= ‘2018-06-01’
set @Date_end=getdate()
SET @i=1
SET @Counter=0
SET @Counter2=0
SET @Counter3=0
SET @CharPool = ‘0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’
–设置随机序列

WHILE @i<@num
BEGINSET
@test_int=rand()*1000
SET @Counter= cast( floor(rand()*61) as int)
SET @Counter2= cast( floor(rand()*61) as int)
SET @RandomStr = SUBSTRING(@CharPool, @Counter ,1)
SET @RandomStr2 = SUBSTRING(@CharPool, @Counter2 ,1)
SET @RandomStr3 = @RandomStr+@RandomStr2+SUBSTRING(@CharPool, @Counter3 ,2)
select @test_date=dateadd(minute,abs(checksum(newid()))%(datediff(minute,@Date_start,@Date_end)+1),@Date_start)
set @test_decimal=@NumBegin+round((@NumEnd-@NumBegin)*rand(),@Decimal)
set @test_float=@NumBegin+round((@NumEnd-@NumBegin)*rand(),@Decimal)
INSERT INTO [sqlserver_src].[dsp_test_src].[dxp_gp_1w] ([test_bigint], [test_char], [test_date], [test_datetime], [test_decimal], [test_float], [test_int], [test_varchar], [test_text])
VALUES
(@i, @RandomStr, @test_date, @test_date, @test_decimal, @test_float, @test_int,@RandomStr2, @RandomStr3);SET @i=@i+1ENDEXEC add_list @num=10000