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

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

/*先清空carflow中的数据,用delete from table删除几十万条数据会非常的慢，因为要写日志文件*/truncate table [saveenergy].[dbo].[carflow]      declare @collectorid1 intselect @collectorid1=collectorid from [saveenergy].[dbo].[collector] where position='1.成双大道商都路交叉口'declare @collectorid2 intselect @collectorid2=collectorid from [saveenergy].[dbo].[collector] where position='2.成双大道商都路交叉口'declare @collectorid3 intselect @collectorid3=collectorid from [saveenergy].[dbo].[collector] where position='藏卫路北三段五岔路口'/*设置数据库各列变量*/declare @time datetimeset @time='2011-07-11 00:00:00'declare @i intset @i=1declare @now datetimeset @now=(select GETDATE())declare @maxi intset @maxi=(select datediff(MINUTE,'2011-07-11 00:00:00',@now))declare @densityleft floatdeclare @densityright floatdeclare @leftflow intdeclare @rightflow intdeclare @tmpdens floatdeclare @tmpflow intwhile @i<@maxibegin 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+1endGO

原文链接: https://www.cnblogs.com/justinzhang/archive/2011/07/25/2116697.html

欢迎关注

微信关注下方公众号，第一时间获取干货硬货；公众号内回复【pdf】免费获取数百本计算机经典书籍