SQL While Loop is Is Printing same ID for Every Record-CodePudding

My resultset has 2958 rows. I'm trying to Loop over the result set and select each row, assigning a new id to it, but each new Id is printing 2958 times. What am I doing wrong and how could I fix this? After the select is correct, I want to use it with the Insert.

DECLARE @ID AS INT
SET @ID = 16342 -- Next Available ID
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(ID) FROM [dbo].[Table1] WHERE ID2 is null)
DECLARE @I INT = 1


WHILE (@I <= @RowCount)
BEGIN
    SET IDENTITY_INSERT [Table1] ON
--INSERT INTO [dbo].[Table1] 
--(@ID, Code, Description1, Description2,Field1,Field2,Field3,ID2)

    PRINT @ID

    ((SELECT @ID, Code, Description`, Description2,Field1,Field2,Field3,15 as ID2
        FROM [dbo].[Table1] 
        WHERE ID2 is null))
    
    SET @ID = @ID   1
    SET @I = @I  1
    SET IDENTITY_INSERT [Table1] OFF
    
END

CodePudding user response：

The main problem in your loop, I think, is that you're just selecting all the rows instead of specifying which row you want for this iteration of the loop.

But you don't need a loop for this at all. You're building a loop based on the count of a specific set of rows in the source table; just use those same rows to generate a set.

DECLARE @ID int = 16342; -- Next Available ID - how do you get this?
  -- if that is the current MAX(ID) then just use the identity property
  -- after all, it's there so you don't have to hard-code values

SET IDENTITY_INSERT dbo.Table1 ON;

WITH src AS 
(
  SELECT ID, Code, Description, Description2, 
         Field1, Field2, Field3, ID2, 
         rn = ROW_NUMBER() OVER (ORDER BY ID2)
  FROM dbo.Table1 WHERE ID2 IS NULL
)
/* uncomment this when you prove it's working
INSERT dbo.Table1
(
  ID, Code, Description, Description2, 
  Field1, Field2, Field3, ID2
)
*/
SELECT @ID   rn, Code, Description, Description2,
  Field1, Field2, Field3, 15
FROM src;

SET IDENTITY_INSERT dbo.Table1 OFF;