TSQL批处理插入一万条记录-随机生成某些字段值

你的名字 2023-01-18 09:17 48阅读 0赞

student2表目前是空的；下面来插入一万条记录；

![20210502213519743.PNG][]

执行；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70][]

代码；

declare @i int, @id int, @name varchar(32), @ex char(10), @sexrnd int        
    set @i = 0
    while @i < 10000
      begin
        set @id = @i + 1    
        set @name='testname' + STR(@id)    
        set @sexrnd= cast((rand()*(3-1)+1) as int)
        if @sexrnd=1
            set @ex='男'
        else
            set @ex='女'    
    
        insert into student2 values(@id,@name, @ex)
        
        set @i = @i +1
      end

看一下执行结果；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 2][]

每次生成sname字段值用 'testname' + STR(@id)   ，中间有空格，是STR函数引起的；

然后来看一下SQL随机数的问题；

首先，RAND()函数，生成的是小数；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 3][]

rand() \* (5-1) + 1，生成的是1-5之间的小数；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 5][]

生成整数使用cast函数，如下；下面语句生成1-3之间的随机整数，要么是1要么是2；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 7][]

set @sexrnd= cast((rand()\*(3-1)+1) as int)  
    if @sexrnd=1  
        set @ex='男'  
    else  
        set @ex='女'

根据随机数来设置性别为男或女；

[20210502213519743.PNG]: /images/20221021/51574fcdf3ef48b48a1f30138ac38b05.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70]: /images/20221021/9533f28ef7ba47a991637dc03a6ca8e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 1]: /images/20221021/51c6b40ad2d64bafaa98ea282077a938.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 2]: /images/20221021/93c7b83d2b9640828592407edcdbf739.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 3]: /images/20221021/4bd5c255e427439cb75883953ca792e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 4]: /images/20221021/144526e2de3f4319a53b2c17660a4765.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 5]: /images/20221021/2dc2964f742a4b98896459f4aa7dfa08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 6]: /images/20221021/bddc8efb0f7349f4a914df66be4f0f3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JjYm9ibzIxY24_size_16_color_FFFFFF_t_70 7]: /images/20221021/4f8a3dcdbab54ea4bdb768ab58524718.png