Accumulate a number every ten seconds

我是杀人狂

LS is strong, does SQL have this~~

hiqwolfzzh

--Basic steps to create a job through the wizard

(1) Enterprise Manager-Management-SQL Server Agent-Jobs
(2) Right-click job-new job
(3) Conventional label
--Enter the job name in the name
-If you do not want the job to run immediately after creation, clear the Enable check box
   For example, if you want to test a job before running on schedule, disable the job.
--If the job only runs on this server, click the option to target the local server
--If the server is the main server and the job runs on other servers, click the option to target multiple servers
--Enter the description of the job function in the description box, the maximum number of characters is 512
(4) Step Label-New
--Enter the step name in the step name
--Select Transact-SQL script (TSQL) in the type
--Database select the database to execute the command
--Enter the statement to be executed in the command, such as EXEC stored procedure name...
--determine
(5) Scheduling Tab-New Scheduling
--Enter the scheduling name in the name
--Select the execution method of your job in the scheduling type
(If you choose to appear repeatedly, you can click Change to set the way you repeat the job)

(6) Start the SQL Agent service and set it to start automatically, otherwise your job will not be executed
--Open the SQL SERVER service manager--click to continue--will automatically start the service when the OS is started
--Or My Computer--Control Panel--Administrative Tools--Services--Right-click SQLSERVERAGENT--Properties--Startup Type--Select Auto Start--OK.





############################################## ##

--Pass the code:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_createjob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_createjob]
GO

create proc p_createjob
@jobname varchar(100), --job name
@sql varchar(8000), - the command to be executed
@serverName sysname='', --job server name
@dbname sysname='', --The default is the current database name
@freqtype varchar(6)='day', --time period, month month, week week, day day
@fsinterval int=1, - relative to the number of repetitions per day
@time int=170000 --Start execution time, for repeated jobs, it will be from 0 o'clock to 23:59
as
if isnull(@dbname,'')='' set @dbname=db_name()

--Create job
exec msdb..sp_add_job @job_name=@jobname

--Create job steps
exec msdb..sp_add_jobstep @job_name=@jobname,
        @step_name ='Data processing',
        @subsystem ='TSQL',
        @database_name=@dbname,
        @command = @sql,
        @retry_attempts = 5, --number of retries
        @retry_interval = 5 ---retry interval

--Create schedule
declare @ftype int,@fstype int,@ffactor int
select @ftype=case @freqtype when'day' then 4
                                        when'week' then 8
                                        when'month' then 16 end
        ,@fstype=case @fsinterval when 1 then 0 else 8 end
if @fsinterval<>1 set @time=0
set @ffactor=case @freqtype when'day' then 0 else 1 end

EXEC msdb..sp_add_jobschedule @job_name=@jobname,
        @name ='Schedule',
        @freq_type=@ftype, --daily, 8 weekly, 16 monthly
        @freq_interval=1, - number of repeated executions
        @freq_subday_type=@fstype, --Whether to repeat execution
        @freq_subday_interval=@fsinterval, --repetition period
        @freq_recurrence_factor=@ffactor,
        @active_start_time=@time - execute at 17:00:00 PM

if @servername=''
set @servername=@@servername
EXEC msdb..sp_add_jobserver @job_name = @jobname,
     @server_name = @servername

go

--transfer
--- Operations performed every day
exec p_createjob @jobname='dd'
                 ,@sql='insert into table 2(...) select ... from table 1 where ...'
                 ,@servername='job server name'
                 ,@dbname='database name'
                 ,@freqtype='day'
                 ,@time='000000'

wgfxman

In addition to the JOB execution that everyone said, you can also use the following methods:

declare @Last_hour char(2)
select @Last_hour=convert(char(2),getdate(),108)
while 1=1 (loop execution, you can also decide whether to stop executing the process by accessing a certain global variable or a certain table parameter)
begin
    waitfor DELAY '00:00:10'-wait for 10 seconds
    update L set L=L+CAST(rand()*1000 as INT) FROM table. . (The rand()*1000 depends on the situation to define by yourself)
    IF @Last_hour<>convert(char(2),getdate(),108) one hour passed
    BGEIN
        Generate a record to the table
        select @Last_hour=convert(char(2),getdate(),108)
    END
end
   
In this way, the tasks to be performed on the hour may differ by 0-10 seconds
However, you can also move the task to be performed on the whole point to another process for execution. The algorithm is similar, which can reduce the time error.