Set interval of insert query of 10 second.

Question

i want to set interval in insert query .

Declare @OrderNo int  
Declare @Order_Ref_No varchar(50)  
Declare @PType varchar(10)  
Declare @Date date  
Declare @PStatus int  
Declare @IPaddress varchar(50)  
Declare @Etime time(7)  
Declare @Chck_Status varchar(50)  
  
  
  
SET NOCOUNT ON;  
if Not EXISTS  
  
(   
select ETime,IPaddress   
from tbl_BalPacM  
where ETime='18:15:08' and IPaddress='192.168.10.10'   
)  
  
  
Begin  
  
  
    INSERT INTO tbl_BalPacM (OrderNo,Order_Ref_No,PType,Date,PStatus,IPaddress,Etime,Chck_Status) VALUES (22,'AS-102',1,'2021-06-08',1,'192.168.10.10','18:15:08','scn')  
	  SELECT SCOPE_IDENTITY()  
    end  

if last record insert at time (Etime 18:15:08) then next record insert after (ETime 18:15:18)

Answer 1

Hi @Analyst_SQL ,

You could refer Guoxiong's method using WHILE loop with WAITFOR DELAY.

Or refer below simple example:

WHILE 1=1   
BEGIN  
	 INSERT INTO tbl_BalPacM (OrderNo,Order_Ref_No,PType,Date,PStatus,IPaddress,Etime,Chck_Status)   
	 VALUES (22,'AS-102',1,cast(getdate() as date),1,'192.168.10.10',cast(getdate() as time(7)),'scn')  
  
    WAITFOR DELAY '00:00:10.000';  
END  

Actually one better solution is to use a SQL Agent job and schedule it for every ten seconds.

You have to create one job with insert statement and use the stored procedure sp_add_schedule to create the schedule. The parameter @freq_subday_type = 0x2 sets a daily recurrence to seconds and @freq_subday_interval = 10 makes it ten seconds.

For example:

EXEC sp_add_schedule 'every10seconds', 1, 4, 4, 0x2, 10  

You could also refer Create and Attach Schedules to Jobs for more details.

In addition, you'll need to keep an eye on logging since every 10 seconds is 6 times more log entries than every minute.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Not sure what you want to do. I guess you may need a WHILE loop with WAITFOR DELAY inside it. Here is the example code:

DECLARE @tbl TABLE (  
	ProcessDate date,  
	ProcessTime time,  
	IPAddress varchar(16)  
);  
DECLARE @processDateTime datetime2(7) = GETDATE();  
DECLARE @processDate date = CAST(@processDateTime AS date);  
DECLARE @processTime time = CAST(@processDateTime AS time(7));  
DECLARE @ipAddress varchar(16) = '192.168.0.1';  
DECLARE @i int = 1;  
  
WHILE @i <= 10  
BEGIN  
	INSERT INTO @tbl(ProcessDate, ProcessTime, IPAddress)  
	VALUES(@processDate, @processTime, @ipAddress);  
  
	WAITFOR DELAY '00:00:10';  
	  
	SET @processDateTime = GETDATE();  
	SET @processDate = CAST(@processDateTime AS date);  
	SET @processTime = CAST(@processDateTime AS time(7));  
  
	SET @i = @i + 1;  
END  
  
SELECT * FROM @tbl;  

The output from the above query is: