It's working exactly as expected. You get the value of @RecordedTimeConstant to be the current UTC time, and then all your inserts are that value plus 10 seconds. If you SELECT the 2 values of @RecordedTimeConstant and @RecordedTime you'll see that they are 10 seconds apart. You aren't adding 10 seconds to @RecordedTime each time, you're doing the same expression each iteration and setting @RecordedTime to be DATEADD(SECOND, 10, @RecordedTimeConstant) ; as @RecordedTimeConstant 's value is constant, then so too is that value +10 seconds.

Presumably you want each row to have 10 more seconds added to it, not every row have 10 seconds added to the time at the start of the batch. I would, personally, use a tally (I use GENERATE_SERIES here, as I assume you're using the latest version of SQL Server, however, on older versions you can use something like fntally ), which you can also use to create multiples of 10. You'll also, however, need to change your method to create a random number, and RAND won't be appropriate here:

INSERT INTO dbo.mytable (sensor_values,
                         recorded_time)
SELECT ABS(CHECKSUM(NewId())) % 5 + 20,
       DATEADD(SECOND,10*GS.Value,GETUTCDATE())
FROM GENERATE_SERIES(1,1000,1) GS;

INSERT INTO dbo.mytable (sensor_values,
                         recorded_time)
SELECT ABS(CHECKSUM(NewId())) % 4 + 38,
       DATEADD(SECOND,10*GS.Value,GETUTCDATE())
FROM GENERATE_SERIES(1,2000,1) GS;

Note that the timer starts again for the second INSERT (so the first value will be lower than last value of the first INSERT ), as I didn't know if you wanted to reset the value or not.

Using a tally avoids the use of a WHILE loop, which are known to perform (very) poorly.

db<>fiddle

Here's a revised version of your original SQL that produces the desired 10-second increment on each row.

DECLARE @Counter INT 
DECLARE @RecordedTime DATETIME 
DECLARE @RecordedTimeConstant DATETIME=GETUTCDATE()

SET @Counter = 1

/* First loop to create mock data for Area temperature*/
/* generating 1000 values between 20 and 25*/
/* Recorded time in 10 second increments*/

SET @RecordedTime=@RecordedTimeConstant
WHILE (@Counter <= 1000)
BEGIN
    SET @RecordedTime= DATEADD(SECOND, 10, @RecordedTime)
    INSERT INTO [dbo].[mytable] ([sensor_values],[recorded_time])
    VALUES (RAND()*(25-20)+20,@RecordedTime)
    SET @Counter  = @Counter  + 1
END

/* Second loop to create mock data for Area humidity sensor readings*/
/* Same timestamps so can match Area temperature and Area humidity readings*/
/* AreaHumidity values generated are between 38 and 42*/

SET @RecordedTime=@RecordedTimeConstant
WHILE (@Counter <= 2000)
BEGIN
    SET @RecordedTime= DATEADD(SECOND, 10, @RecordedTime)
    INSERT INTO [dbo].[mytable] ([sensor_values],[recorded_time])
    VALUES (RAND()*(42-38)+38,@RecordedTime)
    SET @Counter  = @Counter  + 1
END