使用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