SQL Server 循环插入数据

关键语句

1 Declare @i int = 0         --说明循环次数
2 -- 说明需要插入的列值
3 WHILE @i< 需要插入的次数)
4 BEGIN
5     -- 需要写入数据的值
6     Insert INTO 表名（列名）
7         Values(属性值)
8 END

例子

 1 DECLARE @i int = 0
 2 declare @OrderKey varchar(50),
 3     @PlcNum varchar(3),
 4     @PlcStorageType varchar(1),
 5     @PlcStorageAdd int ,
 6     @PlcOperationType varchar(15) ,
 7     @PlcDataType varchar(10),
 8     @PlcReadNum int,
 9     @PlcWriteNum int,
10     @PlcIsResident bit,
11     @PlcIsPulse bit,
12     @Interval int
13 WHILE @i<20000      --执行插入语句的次数
14 BEGIN
15     set @PlcNum = '0' +CAST( Cast( RAND() * 3 as int) + 1  as varchar(1))  -- PLC编号
16     set @PlcStorageType = SUBSTRING ( 'DM' ,CAST( RAND()*2 as int) + 1, 1 )        --寄存器类型
17     set @PlcStorageAdd = CAST( RAND()*500 as int) + 1                        --寄存器地址
18     set @PlcOperationType = (select Top 1 Names from PlcOperationType  order by newid())    --操作类型    
19     set @PlcDataType =( select Top 1 Names from PlcDataType  order by newid())                --数据类型
20     set @PlcReadNum = CAST( RAND()*5 as int) + 1            --读取数量
21     set @PlcWriteNum  = CAST( RAND()*5 as int) + 1            --写入数量
22     set @PlcIsResident  =  CAST( RAND()*2 as int)            --是否常驻
23     set @PlcIsPulse  = CAST( RAND()*2 as int)                --是否脉冲
24     set @Interval = 300                                        --间隔
25     set @OrderKey  = @PlcNum + @PlcStorageType + CAST( @PlcStorageAdd as varchar) + @PlcOperationType
26 
27     Insert Into OrderDemo(
28         OrderKey , PlcNum , PlcStorageType , PlcStorageAdd ,PlcOperationType ,PlcDataType,
29         PlcReadNum,PlcWriteNum,PlcIsResident,PlcIsPulse ,PlcCommand ,PlcCommand_Ascii ,Interval
30     )values(
31         @OrderKey , @PlcNum , @PlcStorageType , @PlcStorageAdd ,@PlcOperationType ,@PlcDataType,
32         @PlcReadNum, @PlcWriteNum,@PlcIsResident,@PlcIsPulse ,null ,null ,@Interval
33     )
34     set @i = @i + 1
35 END

 

posted @ 2019-09-19 10:42  陆陆无为而治者  阅读(10625)  评论(0)  编辑  收藏  举报