0

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?

Zapnologica
  • 22,170
  • 44
  • 158
  • 253

0 Answers0