使用sql脚本，每隔一分钟，向数据库中插入一条记录

以前从来没有写个sql脚本，由于项目需要，我必须往数据库中插入自上周以来，到现在的每一分钟的数据，刚开始的时候，以为用C++连接上数据库，然后每隔一分钟往表中插入数据，但是觉得好像也不是很方便，所以就选择使用SQL语言来实现了，事实证明SQL脚本是非常强大的，对于日期的各种操作、对于随机数的支持，直接贴代码，备忘：

/*先清空carflow中的数据,用delete from table删除几十万条数据会非常的慢，因为要写日志文件*/
truncate table [saveenergy].[dbo].[carflow]
      
declare @collectorid1 int
select @collectorid1=collectorid from [saveenergy].[dbo].[collector] where position='1.成双大道商都路交叉口'

declare @collectorid2 int
select @collectorid2=collectorid from [saveenergy].[dbo].[collector] where position='2.成双大道商都路交叉口'

declare @collectorid3 int
select @collectorid3=collectorid from [saveenergy].[dbo].[collector] where position='藏卫路北三段五岔路口'


/*设置数据库各列变量*/
declare @time datetime
set @time='2011-07-11 00:00:00'

declare @i int
set @i=1
declare @now datetime
set @now=(select GETDATE())

declare @maxi int
set @maxi=(select datediff(MINUTE,'2011-07-11 00:00:00',@now))

declare @densityleft float
declare @densityright float

declare @leftflow int
declare @rightflow int

declare @tmpdens float
declare @tmpflow int


while @i<@maxi
begin
 set @leftflow=(SELECT CAST( FLOOR(RAND()*50) AS INT) )
 set @rightflow=(SELECT CAST( FLOOR(RAND()*50) AS INT) )
 set @time=(select dateadd(MINUTE,1,@time))
 set @densityleft=(select ROUND(rand(),3))
 set @densityright=(select ROUND(rand(),3))
 

 
 if @leftflow<@rightflow 
 begin
  if @densityleft>@densityright
    begin
        
        set @tmpdens=@densityleft
        set @densityleft=@densityright
        set @densityright=@tmpdens
    end
 end
 
 if @leftflow>@rightflow
 begin
    if @densityleft<@densityright
    begin
        set @tmpdens=@densityleft
        set @densityleft=@densityright
        set @densityright=@tmpdens
    end
 end
  
INSERT INTO [saveenergy].[dbo].[carflow]
           ([collectorid]
           ,[leftflow]
           ,[rightflow]
           ,[flowtime]
           ,[trafficdensityleft]
           ,[trafficdensityright])
     VALUES
           (
           @collectorid1
           ,@leftflow
           ,@rightflow
           ,@time
           ,@densityleft
           ,@densityright)
    
    

 

 /*22222222222222222*/
 
 
 set @leftflow=(SELECT CAST( FLOOR(RAND()*50) AS INT) )
 set @rightflow=(SELECT CAST( FLOOR(RAND()*50) AS INT) )
 
 set @densityleft=(select ROUND(rand(),3))
 set @densityright=(select ROUND(rand(),3))
 
 if @leftflow<@rightflow 
 begin
  if @densityleft>@densityright
    begin
        
        set @tmpdens=@densityleft
        set @densityleft=@densityright
        set @densityright=@tmpdens
    end
 end
 
 if @leftflow>@rightflow
 begin
    if @densityleft<@densityright
    begin
        set @tmpdens=@densityleft
        set @densityleft=@densityright
        set @densityright=@tmpdens
    end
 end
           
   INSERT INTO [saveenergy].[dbo].[carflow]
           ([collectorid]
           ,[leftflow]
           ,[rightflow]
           ,[flowtime]
           ,[trafficdensityleft]
           ,[trafficdensityright])
     VALUES
           (
           @collectorid2
           ,@leftflow
           ,@rightflow
           ,@time
           ,@densityleft
           ,@densityright)


/**3333333333333333333*/

    set @leftflow=(SELECT CAST( FLOOR(RAND()*50) AS INT) )
 set @rightflow=(SELECT CAST( FLOOR(RAND()*50) AS INT) )
 
 set @densityleft=(select ROUND(rand(),3))
 set @densityright=(select ROUND(rand(),3))
 
if @leftflow<@rightflow 
 begin
  if @densityleft>@densityright
    begin
        
        set @tmpdens=@densityleft
        set @densityleft=@densityright
        set @densityright=@tmpdens
    end
 end
 
 if @leftflow>@rightflow
 begin
    if @densityleft<@densityright
    begin
        set @tmpdens=@densityleft
        set @densityleft=@densityright
        set @densityright=@tmpdens
    end
 end
           
   INSERT INTO [saveenergy].[dbo].[carflow]
           ([collectorid]
           ,[leftflow]
           ,[rightflow]
           ,[flowtime]
           ,[trafficdensityleft]
           ,[trafficdensityright])
     VALUES
           (
           @collectorid3
           ,@leftflow
           ,@rightflow
           ,@time
           ,@densityleft
           ,@densityright)
   set @i=@i+1
end

GO