SQL Server 循环插入数据

关键语句

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

例子

DECLARE @i int = 0
declare @OrderKey varchar(50),
    @PlcNum varchar(3),
    @PlcStorageType varchar(1),
    @PlcStorageAdd int ,
    @PlcOperationType varchar(15) ,
    @PlcDataType varchar(10),
    @PlcReadNum int,
    @PlcWriteNum int,
    @PlcIsResident bit,
    @PlcIsPulse bit,
    @Interval int
WHILE @i<20000      --执行插入语句的次数
BEGIN
    set @PlcNum = '0' +CAST( Cast( RAND() * 3 as int) + 1  as varchar(1))  -- PLC编号
    set @PlcStorageType = SUBSTRING ( 'DM' ,CAST( RAND()*2 as int) + 1, 1 )        --寄存器类型
    set @PlcStorageAdd = CAST( RAND()*500 as int) + 1                        --寄存器地址
    set @PlcOperationType = (select Top 1 Names from PlcOperationType  order by newid())    --操作类型    
    set @PlcDataType =( select Top 1 Names from PlcDataType  order by newid())                --数据类型
    set @PlcReadNum = CAST( RAND()*5 as int) + 1            --读取数量
    set @PlcWriteNum  = CAST( RAND()*5 as int) + 1            --写入数量
    set @PlcIsResident  =  CAST( RAND()*2 as int)            --是否常驻
    set @PlcIsPulse  = CAST( RAND()*2 as int)                --是否脉冲
    set @Interval = 300                                        --间隔
    set @OrderKey  = @PlcNum + @PlcStorageType + CAST( @PlcStorageAdd as varchar) + @PlcOperationType

    Insert Into OrderDemo(
        OrderKey , PlcNum , PlcStorageType , PlcStorageAdd ,PlcOperationType ,PlcDataType,
        PlcReadNum,PlcWriteNum,PlcIsResident,PlcIsPulse ,PlcCommand ,PlcCommand_Ascii ,Interval
    )values(
        @OrderKey , @PlcNum , @PlcStorageType , @PlcStorageAdd ,@PlcOperationType ,@PlcDataType,
        @PlcReadNum, @PlcWriteNum,@PlcIsResident,@PlcIsPulse ,null ,null ,@Interval
    )
    set @i = @i + 1
END