I am trying to write a sql script that inserts 1000 rows into the table but increments the day every 1000 rows.
this is what I have so far:
DECLARE @StartDate Date = GetDate();
DECLARE @EndDate Date = DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1) ;
DECLARE @i Date=@StartDate;
DECLARE @j int=0;
DECLARE @sql1 NVARCHAR(4000);
WHILE @i <= @EndDate
BEGIN
WHILE @j <= 1000
BEGIN
-- Create new file group for day
SET @sql1 = N'USE [FSK_LoggingServer]
INSERT INTO [dbo].[LogsIn]
([messageId]
,[serverId]
,[time]
,[direction]
,[hasRouting]
,[selfRouting]
,[deviceType]
,[unitId]
,[accountCode]
,[clientId]
,[data])
VALUES
(1234,
2
,'''+ CONVERT(nchar(10), @i,103) +'''
,1
,1
,1
,0
,123456
,''FSKDEV''
,''hmhjmhjm''
,''data'')'
--RAISERROR (@sql1,0,0)
EXEC sp_executesql @sql1;
SET @j= @j +1
END
SET @i= DATEADD(dd, 1, @i) ;
END
GO
But I am having a issue, it only runs the inner while loop. So it inserts 1000 rows for one day then stops?