SQL While Loop Insert with values from another table

Question

I am trying to create a SQL While loop that will update a temp table with values from another table. Values from the other table:

Wrote the following, however, it only inserts the last value multiple times over.

Here is the code:

USE Reports
GO
    CREATE TABLE #TempTable (CreatedByID int, LastUpdatedByID int, ID int, 
AlertDE int, Alert char(50), StartDTTM datetime, EndDTTM datetime, 
IsInactiveFLAG char(1),AlertDetails char(1));
    DECLARE @numrows INT
    SELECT @numrows  = COUNT(*) FROM [Reports].[dbo].[Eligible]
    DECLARE @id int 
    DECLARE @LoopCount INT = 1
    DECLARE @count int = @numrows
    SELECT  @id = [id] FROM [Reports].[dbo].[Eligible]
WHILE (@LoopCount <= @count)
BEGIN  
    INSERT INTO #TempTable (CreatedByID, LastUpdatedByID, ID, AlertDE, Alert, StartDTTM, EndDTTM, IsInactiveFLAG,AlertDetails) 
    VALUES (52,52,@id,0,'Eligible',CURRENT_TIMESTAMP,'1900-01-01 
00:00:00.000','N','') 
    SET @LoopCount = @LoopCount + 1
END 
SELECT * FROM #TempTable
DROP TABLE #TempTable

I am assuming I have to tell it to loop through the values in the other table somehow but I am not positive if that is the right approach or if in general I am taking the long way around the bus.

Johan Maes · Accepted Answer · 2020-02-13 13:37:54Z

9

Why are you using a loop? You can do this with an insert . . . select statement:

INSERT INTO #TempTable (CreatedByID, LastUpdatedByID, ID, AlertDE, Alert, StartDTTM, EndDTTM, IsInactiveFLAG, AlertDetails) 
    SELECT 52, 52, e.id, 0, 'Eligible', CURRENT_TIMESTAMP, '1900-01-01 00:00:00.000', 'N', ''
    FROM [Reports].[dbo].[Eligible] e ;

See eg https://www.w3schools.com/sql/sql_insert_into_select.asp for more info.

edited Feb 13, 2020 at 13:37

Johan Maes

1,49117 silver badges18 bronze badges

answered May 5, 2018 at 0:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GMR Over a year ago

Thus me mentioning the "long way around the bus" LOL! This reminds me of the time my friend showed me that the arrow on the gas gauge indicates which side of the car the gas cap is on.

Gordon Linoff Over a year ago

@GMR ... I'm surprised you unaccepted the answer. Is there a reason?

SpeedOfRound Over a year ago

I found my way here because my DB has triggers that don't allow multiple rows to be inserted at a time.

anonymiss · Accepted Answer · 2018-05-07 21:22:34Z

GMR, I found a way to accomplish my need which is similar to yours. Hopefully this will help you too.

DECLARE
  @LoopId  int
 ,@TheOrderNumber  varchar(20)

DECLARE @CheckThisItem TABLE
 (
   LoopId  int  not null  identity(1,1)
  ,TheOrderNumber varchar(20)  not null
 )

INSERT @CheckThisItem 
SELECT Order_Number AS TheOrderNumber
FROM [dbo].[Table_Storing_Order_Number] ORDER BY Order_Number ASC
SET @LoopId = @@rowcount
WHILE @LoopId > 0
   BEGIN  
     SELECT @TheOrderNumber = TheOrderNumber
     FROM @CheckThisItem
     WHERE LoopId = @LoopId
-- Start inserting record pulled for while loop
        INSERT [dbo].[The_Destination_Table]
            SELECT TOP (1)
            A, B, C, D
            FROM [dbo].[Source_Table] ST
            WHERE 
            ST.Order_Number = @TheOrderNumber
        -- Set number to reduce loop counter
    SET @LoopId = @LoopId - 1
 END;

Collectives™ on Stack Overflow

SQL While Loop Insert with values from another table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related