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
有志者,事竟成,破釜沉舟,百二秦关终属楚; 苦心人,天不负,卧薪尝胆,三千越甲可吞吴。